Facebook Sign in | Join
Pushing database changes needn't be hard work with SQL Compare
Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

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

  1. The value should be in the format of XXX-XX-XXXX where each X represents a digit
  2. The first three digits (Area number) cannot be between 734 and 749.
  3. The first three digits (Area number) cannot be higher than  772
  4. The first three digits (Area number) cannot be 666
  5. 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
  6. 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:

  1. Remember the key here is to write the shortest code
  2. CLR Functions not allowed
  3. 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)

Share

Comments

# re: TSQL Challenge 4

Thursday, April 23, 2009 6:56 PM by Antoine Gémis

This one was very funny. Thanks !


# re: TSQL Challenge 4

Wednesday, March 24, 2010 1:49 PM by MercuryViper

Jacob, I realise that I'm kinda late to this particular party, but I'm curious as to when rule 6 for this challenge will ever need to be evaluated given the presence of rule 3. If the Area Number cannot exceed 772, how can 987 (the AN in rule 6) be reached?


# re: TSQL Challenge 4

Thursday, March 25, 2010 5:07 AM by Jacob Sebastian

You are right. The challenge was to write the shortest code. The above two rules were added to confuse people :-)


Copyright © Rivera Informatic Private Ltd.