One of the fascinating aspects of SQL Server is the ability to solve puzzles and come up with creative solutions. In this article, we will explore a recent puzzle presented by SQL Server MVP Jacob Sebastian on his blog. The challenge is to write the shortest T-SQL code that removes invalid Social Security Number (SSN) values and returns a result set with only valid SSN values.
Before we dive into the solution, let’s take a look at the additional validation rules for SSN values:
- The SSN should be in the format ‘xxx-xx-xxxx’.
- The first three digits (xxx) should be between 001 and 733 or between 750 and 772.
- The first three digits (xxx) should not be 666.
- The second two digits (xx) should be between 01 and 99.
- The last four digits (xxxx) should be between 0001 and 9999.
Now, let’s take a look at the proposed solution:
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 uses a common table expression (CTE) to separate the SSN into three parts: A, B, and C. It then reconstructs the SSN by appending ‘-‘ at the appropriate places (A + ‘-‘ + B + ‘-‘ + C). This reconstructed SSN is compared with the original SSN to validate the format.
The next part of the code ensures that each section of the SSN falls within the specified range and does not contain any other characters. Instead of comparing the digits directly to integers, they are casted as VARCHAR with single quotes around them. This allows for a simple comparison using the BETWEEN operator.
Now, let’s generate a test table with sample SSN values:
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'
By executing the above code and then running the proposed solution, we will obtain a result set with only the valid SSN values.
I encourage you to participate in this brainstorming session and come up with your own solutions. Can you find an even shorter code to remove invalid SSN values? Share your thoughts and solutions in the comments below!