TSQL Challenge 4
Welcome to TSQL Challenge 4!
This is a simple challenge to write a piece of code that validates an SSN. The key here is to write “shortest” TSQL code that removes invalid SSN values and returns a result set with only valid SSN values.
Validation Rules
- The value should be in the format of XXX-XX-XXXX where each X represents a digit
- The first three digits (Area number) cannot be between 734 and 749.
- The first three digits (Area number) cannot be higher than 772
- The first three digits (Area number) cannot be 666
- It is not allowed to have all zeros in any of the digit group. For example 000-12-1234, 123-00-1234 or 123-12-0000 etc are invalid
- Numbers from 987-65-4320 to 987-65-4329 cannot be used
Here is some sample data.
SET NOCOUNT ON;
DECLARE @t TABLE (SSN VARCHAR(15))
INSERT INTO @t (SSN) SELECT '123-45-6789'
INSERT INTO @t (SSN) SELECT '123-45-67.89'
INSERT INTO @t (SSN) SELECT 'ABC-12-3455'
INSERT INTO @t (SSN) SELECT '123-45-67890'
INSERT INTO @t (SSN) SELECT '123-456789'
INSERT INTO @t (SSN) SELECT ' 123-45-6789'
INSERT INTO @t (SSN) SELECT '12345-6789'
INSERT INTO @t (SSN) SELECT '123456789'
INSERT INTO @t (SSN) SELECT '123-12-1234'
/*
Expected Result:
SSN
---------------
123-45-6789
123-12-1234
*/
Notes:
- Remember the key here is to write the shortest code
- CLR Functions not allowed
- Make sure that the code runs on the above table. (don’t create your own table. That will give me a tough time testing the code. You can add more rows to the above table to test your code with more data. In such cases, include those insert statements when you send me the code)
- Send your entries to jacob@beyondrelational.com
- Do not paste the code in the email body. Include it as an attachment (.sql file)
- Mention ‘TSQL Challenge 4’ in the email subject
- Last Date: 24 April 2009