By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,575 Members | 1,968 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,575 IT Pros & Developers. It's quick & easy.

Validating records in a table

P: 3
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
Feb 9 '10 #1
Share this Question
Share on Google+
1 Reply


nbiswas
100+
P: 149
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

Expand|Select|Wrap|Line Numbers
  1. 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

Expand|Select|Wrap|Line Numbers
  1. 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

Expand|Select|Wrap|Line Numbers
  1. 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

Expand|Select|Wrap|Line Numbers
  1. 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

Expand|Select|Wrap|Line Numbers
  1. Select Passport from tblSample where Passport
  2. 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

Expand|Select|Wrap|Line Numbers
  1.  ID         CaseNumber        PassportNumber
  2.  
  3.   001       100                      X1234567
  4.  
  5.   002       101                      ABCDEFRT78
  6.  
  7.   00A       101                     X1234568
  8.  
  9.   003        XYZ                     P1234567
  10.  
  11.   004       102                      V0987654
Query

Expand|Select|Wrap|Line Numbers
  1. Select ID,CaseNumber,PassportNumber From tblInfos
  2. Where 
  3.  
  4. Id                            LIKE REPLACE('XXX','X','[0-9]')      AND
  5. CaseNumber            LIKE REPLACE('XXX','X','[0-9]')      AND
  6. PassportNumber      LIKE '[A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
Output:
Expand|Select|Wrap|Line Numbers
  1. ID         CaseNumber        PassportNumber
  2.  
  3.   001       100                      X1234567  
  4.   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.
Mar 25 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.