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.
