Validating records in a table

[Banky]

Hi I have a file with the following entities loaded in a temp table

idno
Caseno
passport no

Now I need to validate that this records are avalaible before going to the marching of this values in my temp table with my main table. How do I validate this records in my Temp Table. There are lots of records in the table so I think I will need a cursor. I am new to Sql. Someone pls help me asap

[nbiswas]

Hi, the question is not very clear. Reason

a) What is a valid IdNo? Is it Integer, String/Varchar, Alphanumeric and also of any specified length ... exactly what?

b) The same is applicable for case number too.....

c) Passport number varies across countries. So it is difficult to validate a passport number. Screening can be done through World-Check or a number of other organizations:http://www.world-check.com/en-US/Ser...es/Online.aspx

However, I am first going to give you some case by case sample and then will take you with a larger scenario.

Validation for Id Numbers/ Case Numbers

Case 1 (Assuming only integers and maximum length is 3)

e.g. 001 is a valid Id but neither 01 or A12 or AAA

Select Id from tblSample where Id like REPLACE('XXX','X','[0-9]')

Case 2 (Assuming only Strings and maximum length is 3)

e.g. AAA is a valid Id but neither 01 or A12 or 001

Select Id from tblSample where Id like REPLACE('XXX','X','[A-Z]')

Case 3 (Assuming only alphanumerics and maximum length is 3)

e.g. AA2 is a valid Id but neither 01 or 001

Select Id from tblSample where Id like REPLACE('XXX','X','[A-Z][0-9]')

Case 4 (Assuming a format of xxx-xx-xxx where x is alphanumeric and maximum length is 8)

e.g. AA2 is a valid Id but neither 01 or 001

Select Id from tblSample where Id like REPLACE('xxx-xx-xxx','x','[A-Z][0-9]')

Validation for Indian Passport

A typical Indian passport number is : X1234567

i.e.
- Total length = 8
- Alphanumeric
-First Character is ALPHABET
-Last 7 characters are NUMERICS

Query is

Select Passport from tblSample where Passport
LIKE '[A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

Now take a SampleTable(say tblInfos) whose structure is as under

 ID         CaseNumber        PassportNumber
 
  001       100                      X1234567
 
  002       101                      ABCDEFRT78
 
  00A       101                     X1234568
 
  003        XYZ                     P1234567
 
  004       102                      V0987654

Query

Select ID,CaseNumber,PassportNumber From tblInfos
Where 
 
Id                            LIKE REPLACE('XXX','X','[0-9]')      AND
CaseNumber            LIKE REPLACE('XXX','X','[0-9]')      AND
PassportNumber      LIKE '[A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

Output:

ID         CaseNumber        PassportNumber
 
  001       100                      X1234567  
  004       102                      V0987654

Rules applied for validation are:

- Id/Case Number should be 3 digit numbers

- Valid Indian passport.


Hope this helps.

Now I think with this idea, you can go ahead and implement the same in your project.
All the best.