Oracle Database - how to verify a social security number through a query

Asked By Mansimran Sabharwal on 19-Aug-11 01:53 AM
Hi 
I have a list of ssno and i want to verify whether these are valid or not . It can be thru a regex also.

Regards
mansimran
Devil Scorpio replied to Mansimran Sabharwal on 19-Aug-11 01:57 AM
Hi,

you can validate SSN through javascript

function validateSSN (elementValue){
     var  ssnPattern = /^[0-9]{3}\-?[0-9]{2}\-?[0-9]{4}$/;
     return ssnPattern.test(elementValue);
   }

Explanation:

The argument to this method is the social security number you want to validate.

In the method body we define a variable (‘ssnPattern’) and assign a regular expression to it.

SNN format: The regular expression for social security number (SSN) is

/^[0-9]{3}\-?[0-9]{2}\-?[0-9]{4}$/

To understand the regular expression we will divide it into smaller components:

/^[0-9]{3}:  Means that the social security number must begin with at least three numeric characters.

\-?:   Means that SSN can have an optional hyphen (-) after first 3 digits.

[0-9]{2}: First 3 digits (or optional hyphen) must be followed by 2 more digits.

\-?: After the second group of digits there may be another optional hyphen (-) character.

[0-9]{4}$/: Finally, the social security number must end with four digits.

Devil Scorpio replied to Mansimran Sabharwal on 19-Aug-11 01:58 AM
Hi,

you can validate SSN through javascript

function validateSSN (elementValue){
     var  ssnPattern = /^[0-9]{3}\-?[0-9]{2}\-?[0-9]{4}$/;
     return ssnPattern.test(elementValue);
   }

Explanation:

The argument to this method is the social security number you want to validate.

In the method body we define a variable (‘ssnPattern’) and assign a regular expression to it.

SNN format: The regular expression for social security number (SSN) is

/^[0-9]{3}\-?[0-9]{2}\-?[0-9]{4}$/

To understand the regular expression we will divide it into smaller components:

/^[0-9]{3}:  Means that the social security number must begin with at least three numeric characters.

\-?:   Means that SSN can have an optional hyphen (-) after first 3 digits.

[0-9]{2}: First 3 digits (or optional hyphen) must be followed by 2 more digits.

\-?: After the second group of digits there may be another optional hyphen (-) character.

[0-9]{4}$/: Finally, the social security number must end with four digits.

Mansimran Sabharwal replied to Devil Scorpio on 19-Aug-11 01:59 AM
Thank you for your reply but i want to do this in oracle and  db2
Riley K replied to Mansimran Sabharwal on 20-Aug-11 04:02 AM
Hi,

The regexp functions available in Oracle 10g will help you to achieve the above tasks in a simpler and faster way.

select case when regexp_like('987-65-4321' ,'^[0-9]{3}-[0-9]{2}-[0-9]{4}$') then 'Match Found' else 'No Match Found' end as output from dual;

Output: Match Found

Input: 987-654-3210
Output: No match found

^ start of the string
[0-9]{3} three occurrences of digits from 0-9
- followed by hyphen
[0-9]{2} two occurrences of digits from 0-9
- followed by hyphen
[0-9]{4} four occurrences of digits from 0-9
$ end of the string

The above pattern can also be used to validate phone numbers with little customization.

Cheers


Mansimran Sabharwal replied to Riley K on 23-Aug-11 01:29 AM
The query which you had provided is for a particular case '987-65-4321' and if i replace this with the column name it does not searches anything
Mansimran Sabharwal replied to Riley K on 24-Aug-11 02:28 AM
Thanks for your reply but now i want to do the same thing for sql server and DB2. kindly help me to solve this
Riley K replied to Mansimran Sabharwal on 24-Aug-11 11:05 AM