Solution for Challenge #4

The goal for this challenge was to write the shortest possible code.

I know that the shortest code is not necessarily better performing code.

But nobody will argue that this challenge was a good exercise.

Here is my original solution for the Challenge #4.

select * from @t where ssn
like'[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'and
not(ssn like'000%'or ssn like'%-00-%'or ssn like'%0000'or
ssn like'666%'or ssn>'772'or(ssn>'734'and ssn<'750'))

This is reformatted version to make the code more readable:

SELECT *

FROM @t

WHERE

    ssn LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'   --#1

    AND NOT (    ssn LIKE '000%'                           --not #5

                OR ssn LIKE '%-00-%'                       --not #5

                OR ssn LIKE '%0000'                        --not #5

                OR ssn LIKE '666%'                         --not #4

                OR ssn > '772'                             --not #3

                OR ( ssn > '734' AND ssn < '750' )         --not #2

           )

I'm going to reference the validation rules by their numbers in the Challenge #4 description.

The key to this solution was to use not numerical but textual comparisons.

It allowed shortening the code because for numerical comparisons we would need some logic to tokenize SSN into 3 digital groups.

For rule #1 I use the expression

    ssn like'[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'

that excludes all ssn values that aren't in the format of XXX-XX-XXXX where each X represents a digit.

The wild card [0-9] corresponds to any digit in a range from 0 to 9.

This validation can be expressed even a bit shorter using the approach from Antoine Gémis's solution to this challenge:

    ssn like replace('XXX-XX-XXXX','X','[0-9]')

For #2 I relied on a fact that in a textual comparison any string that is lengthier than 3 characters and starts with group of 3 digits between 734 and 749 is greater than string '734' and less than string '750'

#3 I translated as ssn shouldn't start with a string greater than '772'

#4 I translated as ssn shouldn't start with the string '666'

After format check of XXX-XX-XXXX is applied we can validate rule #5 using % wild card:

   ssn LIKE '000%'        -- excludes ssn that starts with 000  

OR ssn LIKE '%-00-%'      -- excludes ssn that contains -00-                      

OR ssn LIKE '%0000'       -- excludes ssn that ends with 0000              

Validation rule #3 is more restrictive than the rule #6, so if we validate #3 we can omit the check on #6.

Instead of using expression pattern "not like exp1 and not like exp2 and not like..." I used "not (like exp1 or like exp2 or like...)".

It allowed to avoid repetitive "and not"s and as a result make the code shorter.

We can make it a bit shorter removing parenthesis around (ssn>'734' AND ssn<'750') as Pinal Dave did in his solution to this challenge.

Combining all 3 winning solutions I would rewrite the code as the following:

select * from @t where ssn like REPLACE('XXX-XX-XXXX','X','[0-9]')and not(ssn like'000%'or ssn like'%-00-%'or ssn like'%0000'or ssn like'666%'or ssn>'772'or ssn>'734'and ssn<'750')

If you like this article,  Subscribe in a reader or Subscribe by Email. Show your support by sharing this article with your friends through the services given below.

Comments