SQL SERVER – Solution to Puzzle – Shortest Code to Perform SSN Validation

One of my friends – a SQL Server MVP- Jacob Sebastian has a knack for coming up with interesting ideas and stuffs, the latest example being SQL Server Puzzles on his blog. Jacob is a regular blogger and a talented writer. I enjoy reading his blogs and books. He has recently published his new book – The Art of XSD – SQL Server XML Schema Collections.

I have based my present article on his most recent brainteaser – Write the shortest T-SQL Code that removes invalid SSN values and returns a result set with only valid SSN values. There are few additional validation rules that have been listed here.

I’m sure after reading the puzzle you all are bitten by the bug to solve it, and I’m certainly not an exception! I have attempted to solve the puzzle and have so far come up with the following shortest code.

SELECT DISTINCT ssn FROM @t JOIN(SELECT LEFT(SSN,3)A,SUBSTRING(SSN,5,2)B,RIGHT(SSN,4)C FROM @t)D ON A+'-'+B+'-'+C=SSN AND(A>'000'AND A<'734'OR A>'749'AND A<'773')AND A<>'666'AND B>'00'AND B<='99'AND C>'0000'AND C<='9999'

The above code has been adjusted below for better readability:

SELECT DISTINCT ssn
FROM @t
JOIN
(SELECT LEFT(SSN,3) A, SUBSTRING(SSN,5,2) B, RIGHT(SSN,4) C
FROM @t) D
ON A+'-'+B+'-'+C = SSN AND (A > '000' AND A < '734' OR A > '749' AND A < '773')
AND A <> '666' AND B > '00' AND B <= '99' AND C > '0000' AND C <= '9999'

I think my proposed solution is very simple to understand; however, I would still like to give some explanation for your better understanding.

I took the original SSN Number that should be in the format of ‘xxx-xx-xxxx’. I separated all the three parts using CTE (common table expression). After they were separated, I once again attempted to build the original SSN appending ‘-‘ at its appropriate place (A+'-'+B+'-'+C) i.e. 4th and 7th place. Once I recreated SSN, I compared it with the original SSN (A+'-'+B+'-'+C = SSN); this comparison validates the format of the SSN. I must say, this was the most difficult part of solving the puzzle.

The next part is comparatively simple. I  made sure that each section of SSN was falling within a range of integer and does not accept any other character. Now, instead of comparting all the digits to INT, I casted them as VARCHAR with single quotes around them. I used these VARCHAR casted variables with operator BETWEEN and compared INT range. This was the shortest way to validate INT range.

I would like my readers to participate in this brainstorming session and come up the shortest code.

The following is sample code to generate test table.

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 ' 23-45-6789'
INSERT INTO @t (SSN) SELECT '12345-6789'
INSERT INTO @t (SSN) SELECT '123456789'
INSERT INTO @t (SSN) SELECT '123-12-1234'
INSERT INTO @t (SSN) SELECT '666-12-1234'
INSERT INTO @t (SSN) SELECT '123-12-0000'
INSERT INTO @t (SSN) SELECT '000-12-1234'
INSERT INTO @t (SSN) SELECT '735-12-1234'
INSERT INTO @t (SSN) SELECT '987-12-1234'
INSERT INTO @t (SSN) SELECT '123-65-1234'
INSERT INTO @t (SSN) SELECT '987-65-4321'
INSERT INTO @t (SSN) SELECT '987-65-4351'

Reference : Pinal Dave (https://blog.sqlauthority.com)