The ‘SSN matching’ SQL Problem

Question

After we have fixed the performance problems with the codes written by Robert Bar and his brother Reggie Bar, now has the time come to their cousin, Fu Bar.

She was facing a not-so-simple problem by matching a list of user-entered SSN's (from a web page) with a list of validated SSN's. So far so good, right, but as we know, users aren't that particular careful when entering their personal information, so the SSN's have a number of errors in them.
The good thing is that all user-entered SSN's are checksum-validated and have duplicates removed.
All files (setup, sample data population and Fu Bar's code) can be downloaded from here. Fu's code is updated 10am Sunday 16th of May 2010 UTC. If you have downloaded the code before this time, please re-download.

The rules are as follow (in addition to the standard rules here)

1) A perfect match is valid
2) One single digit wrong is valid
3) Two digits wrong are valid, as long as they are not adjacent
4) Three or more digits wrong is invalid
5) If a user-entered SSN has a perfect match with a validated SSN, this user-entered SSN cannot be used in a combination with another validated SSN.
6) You are not allowed to change the schema of the two tables, with indexes as the only exception.

To clarify the non-adjacent rule:
a) A digit wrong in an odd position is allowed with a digit wrong on another odd position.
b) A digit wrong in an even position is allowed with a digit wrong on another even position.
c) A digit wrong in an odd position is not allowed with a digit wrong for any even position. This is because there is one flaw in the checksum algorithm used for the user-entered SSN's.
d) For very same reason as "c)", a digit wrong in an even position is not allowed with a digit wrong for any odd position.

Your task is to rewrite Fu's code (her code takes about 70-75 minutes to run) to produce a list in following style:

vSSN, ueSSN, Status

vSSN is the validated SSN from dbo.Validated table.
ueSSN is the user-entered SSN from the dbo.UserEntered table.
Status column is a tinyint describing how many characters that are wrong. 0 is a perfect match, 1 is one digit wrong match and 2 is a two digit wrong match.

The sorting is vital, because helpdesk will receive this output later for manual correction so an output with order by vSSN, ueSSN and Status is mandatory.

All entries have to be submitted before 9pm Sunday 30th of May 2010 (UTC). Entries will be evaluated as they are submitted and time allows. Final winner will be announced shortly after that.

First Prize for SQL Speed Phreak Challenge #5: $100 and a license for SQL Data Generator (used when creating the sample data) !

There will also be three categorized runner-up prizes:

* Best Cursor-based solution
* Best CLR solution
* Best Unusual/experimental attempt

The winners of these prizes will get a licensed copy of either SQL Prompt or SQL Refactor, whichever they prefer.

Don't forget to have fun and to be creative,

//Peso

Phil Factor:

The SSN problem was one of the most enjoyable to take part in, because the problem as so simple to define, but the answer was tricky to get right. In the end, all the solutions except for cursor-based ones, were performing in under a second, which made the final order rather more difficult to determine.

When differences are in tens of milliseconds, you can get a different order on different machines: This is a problem but when the size of the test data was determined, the existing cursor-based solution was running in fourteen minutes. We never predicted the extent of the Speed Phreakery we could achieve. The first set-based SQL solutions had this down to less than a handful of seconds, and soon the solutions were fighting in around the half-second mark.

The CLR-based solutions probably were at a slight advantage with this problem though it would be nice to see if this is true of different sizes of data. Matt and JAhlen were neck and neck with this, though JAhlen was a node ahead over the finishing line.

Daniel’s SSIS solution was a sober warning that one should never neglect the obvious answers. It gets the award for the Best Unusual/experimental attempt.

Matt was the clear winner of the best SQL solution (OK, Fu Bar was banned from the best Cursor-based solution). I hope he will want the license for Prompt/Refactor, (excellent for quickly formatting code) though he writes his own excellent SQL tools which are well worth a look.

Winner of the First Prize for the SQL Speed Phreak Challenge #5: $100 and a license for SQL Data Generator (used when creating the sample data) was Johan Åhlén (JAhlen).

It was a very difficult decision as to whether Daniel or Johan won since it was a photofinish. We asked various experts, but nobody would venture an opinion. (The actual timings will vary considerably on different servers, be warned. The amount of memory, no. Of CPUs and general machine configuration will make a difference). I almost had to toss a coin!

Kathi Kellenberger, who wrote the excellent http://www.simple-talk.com/sql/performance/set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem/ Set-based Speed Phreakery: The FIFO Stock Inventory SQL Problem and http://www.simple-talk.com/sql/performance/writing-efficient-sql-set-based-speed-phreakery/ Writing Efficient SQL: Set-Based Speed Phreakery, is studying the results of this competition with the intention of writing it up for Simple-Talk like the others.

Many thanks to all. Peso for all the hard work, and for constructing such an interesting problem. Matt for his energy in coming up with both CLR and TSQL solutions, Scott for kicking things off, Fu Bar for trying her best, Daniel for thinking laterally, Johan for showing the way with the CLR routines, and all those whose comments encouraged the participants to greater efforts.

Answer 1

2010.5.16 Scot Hauder v1

I'm titling this one: "Compute Scalar" :)

SELECT val.SSN [vSSN], uen.SSN [ueSSN], 
CASE WHEN SUBSTRING(val.SSN,1,1) = SUBSTRING(uen.SSN,1,1) THEN 0 ELSE 1 END +
CASE WHEN SUBSTRING(val.SSN,2,1) = SUBSTRING(uen.SSN,2,1) THEN 0 ELSE 1 END +
CASE WHEN SUBSTRING(val.SSN,3,1) = SUBSTRING(uen.SSN,3,1) THEN 0 ELSE 1 END +
CASE WHEN SUBSTRING(val.SSN,4,1) = SUBSTRING(uen.SSN,4,1) THEN 0 ELSE 1 END +
CASE WHEN SUBSTRING(val.SSN,5,1) = SUBSTRING(uen.SSN,5,1) THEN 0 ELSE 1 END +
CASE WHEN SUBSTRING(val.SSN,6,1) = SUBSTRING(uen.SSN,6,1) THEN 0 ELSE 1 END +
CASE WHEN SUBSTRING(val.SSN,7,1) = SUBSTRING(uen.SSN,7,1) THEN 0 ELSE 1 END +
CASE WHEN SUBSTRING(val.SSN,8,1) = SUBSTRING(uen.SSN,8,1) THEN 0 ELSE 1 END +
CASE WHEN SUBSTRING(val.SSN,9,1) = SUBSTRING(uen.SSN,9,1) THEN 0 ELSE 1 END [Status]

FROM (
  SELECT v.[SSN], x.SSN2
  FROM [dbo].[Validated] v 

  CROSS APPLY (SELECT SUBSTRING(v.SSN,2,1) + SUBSTRING(v.SSN,4,1) + SPACE(2)
                    + SUBSTRING(v.SSN,1,1) 
                    + SUBSTRING(v.SSN,3,1)
                    + SUBSTRING(v.SSN,5,1)
                    + SUBSTRING(v.SSN,7,1)
                    + SUBSTRING(v.SSN,9,1) [SSN2] UNION ALL
               SELECT SUBSTRING(v.SSN,2,1) + SPACE(1) + SUBSTRING(v.SSN,6,1) + SPACE(1) 
                    + SUBSTRING(v.SSN,1,1) 
                    + SUBSTRING(v.SSN,3,1)
                    + SUBSTRING(v.SSN,5,1)
                    + SUBSTRING(v.SSN,7,1)
                    + SUBSTRING(v.SSN,9,1) UNION ALL
               SELECT SUBSTRING(v.SSN,2,1) + SPACE(2) + SUBSTRING(v.SSN,8,1) 
                    + SUBSTRING(v.SSN,1,1) 
                    + SUBSTRING(v.SSN,3,1)
                    + SUBSTRING(v.SSN,5,1)
                    + SUBSTRING(v.SSN,7,1)
                    + SUBSTRING(v.SSN,9,1) UNION ALL
               SELECT SPACE(1) + SUBSTRING(v.SSN,4,1) + SUBSTRING(v.SSN,6,1) + SPACE(1) 
                    + SUBSTRING(v.SSN,1,1) 
                    + SUBSTRING(v.SSN,3,1)
                    + SUBSTRING(v.SSN,5,1)
                    + SUBSTRING(v.SSN,7,1)
                    + SUBSTRING(v.SSN,9,1) UNION ALL
               SELECT SPACE(1) + SUBSTRING(v.SSN,4,1) + SPACE(1) + SUBSTRING(v.SSN,8,1) 
                    + SUBSTRING(v.SSN,1,1) 
                    + SUBSTRING(v.SSN,3,1)
                    + SUBSTRING(v.SSN,5,1)
                    + SUBSTRING(v.SSN,7,1)
                    + SUBSTRING(v.SSN,9,1) UNION ALL
               SELECT SPACE(2) + SUBSTRING(v.SSN,6,1) + SUBSTRING(v.SSN,8,1)
                    + SUBSTRING(v.SSN,1,1) 
                    + SUBSTRING(v.SSN,3,1)
                    + SUBSTRING(v.SSN,5,1)
                    + SUBSTRING(v.SSN,7,1)
                    + SUBSTRING(v.SSN,9,1) UNION ALL

               SELECT SUBSTRING(v.SSN,2,1) 
                    + SUBSTRING(v.SSN,4,1)
                    + SUBSTRING(v.SSN,6,1)
                    + SUBSTRING(v.SSN,8,1)
                    + SPACE(2) + SUBSTRING(v.SSN,5,1) + SUBSTRING(v.SSN,7,1) + SUBSTRING(v.SSN,9,1) UNION ALL
               SELECT SUBSTRING(v.SSN,2,1) 
                    + SUBSTRING(v.SSN,4,1)
                    + SUBSTRING(v.SSN,6,1)
                    + SUBSTRING(v.SSN,8,1)
                    + SPACE(1) + SUBSTRING(v.SSN,3,1) + SPACE(1) + SUBSTRING(v.SSN,7,1) + SUBSTRING(v.SSN,9,1) UNION ALL
               SELECT SUBSTRING(v.SSN,2,1) 
                    + SUBSTRING(v.SSN,4,1)
                    + SUBSTRING(v.SSN,6,1)
                    + SUBSTRING(v.SSN,8,1)
                    + SPACE(1) + SUBSTRING(v.SSN,3,1) + SUBSTRING(v.SSN,5,1) + SPACE(1) + SUBSTRING(v.SSN,9,1) UNION ALL
               SELECT SUBSTRING(v.SSN,2,1) 
                    + SUBSTRING(v.SSN,4,1)
                    + SUBSTRING(v.SSN,6,1)
                    + SUBSTRING(v.SSN,8,1)
                    + SPACE(1) + SUBSTRING(v.SSN,3,1) + SUBSTRING(v.SSN,5,1) + SUBSTRING(v.SSN,7,1) + SPACE(1) UNION ALL
               SELECT SUBSTRING(v.SSN,2,1) 
                    + SUBSTRING(v.SSN,4,1)
                    + SUBSTRING(v.SSN,6,1)
                    + SUBSTRING(v.SSN,8,1)
                    + SUBSTRING(v.SSN,1,1) + SPACE(2) + SUBSTRING(v.SSN,7,1) + SUBSTRING(v.SSN,9,1) UNION ALL
               SELECT SUBSTRING(v.SSN,2,1) 
                    + SUBSTRING(v.SSN,4,1)
                    + SUBSTRING(v.SSN,6,1)
                    + SUBSTRING(v.SSN,8,1)
                    + SUBSTRING(v.SSN,1,1) + SPACE(1) + SUBSTRING(v.SSN,5,1) + SPACE(1) + SUBSTRING(v.SSN,9,1) UNION ALL
               SELECT SUBSTRING(v.SSN,2,1) 
                    + SUBSTRING(v.SSN,4,1)
                    + SUBSTRING(v.SSN,6,1)
                    + SUBSTRING(v.SSN,8,1)
                    + SUBSTRING(v.SSN,1,1) + SPACE(1) + SUBSTRING(v.SSN,5,1) + SUBSTRING(v.SSN,7,1) + SPACE(1) UNION ALL
               SELECT SUBSTRING(v.SSN,2,1) 
                    + SUBSTRING(v.SSN,4,1)
                    + SUBSTRING(v.SSN,6,1)
                    + SUBSTRING(v.SSN,8,1)
                    + SUBSTRING(v.SSN,1,1) + SUBSTRING(v.SSN,3,1) + SPACE(2) + SUBSTRING(v.SSN,9,1) UNION ALL
               SELECT SUBSTRING(v.SSN,2,1) 
                    + SUBSTRING(v.SSN,4,1)
                    + SUBSTRING(v.SSN,6,1)
                    + SUBSTRING(v.SSN,8,1)
                    + SUBSTRING(v.SSN,1,1) + SUBSTRING(v.SSN,3,1) + SPACE(1) + SUBSTRING(v.SSN,7,1) + SPACE(1) UNION ALL
               SELECT SUBSTRING(v.SSN,2,1) 
                    + SUBSTRING(v.SSN,4,1)
                    + SUBSTRING(v.SSN,6,1)
                    + SUBSTRING(v.SSN,8,1)
                    + SUBSTRING(v.SSN,1,1) + SUBSTRING(v.SSN,3,1) + SUBSTRING(v.SSN,5,1) + SPACE(2))x
  )val
  JOIN
 (SELECT  ue.[SSN], y.SSN2
  FROM [dbo].[UserEntered] ue
  LEFT JOIN [dbo].[Validated] v ON (v.SSN = ue.SSN)
  CROSS APPLY (SELECT SUBSTRING(ue.SSN,2,1) + SUBSTRING(ue.SSN,4,1) + SPACE(2) 
                    + SUBSTRING(ue.SSN,1,1) 
                    + SUBSTRING(ue.SSN,3,1)
                    + SUBSTRING(ue.SSN,5,1)
                    + SUBSTRING(ue.SSN,7,1)
                    + SUBSTRING(ue.SSN,9,1) [SSN2] UNION ALL
               SELECT SUBSTRING(ue.SSN,2,1) + SPACE(1) + SUBSTRING(ue.SSN,6,1) + SPACE(1) 
                    + SUBSTRING(ue.SSN,1,1) 
                    + SUBSTRING(ue.SSN,3,1)
                    + SUBSTRING(ue.SSN,5,1)
                    + SUBSTRING(ue.SSN,7,1)
                    + SUBSTRING(ue.SSN,9,1) UNION ALL
               SELECT SUBSTRING(ue.SSN,2,1) + SPACE(2) + SUBSTRING(ue.SSN,8,1) 
                    + SUBSTRING(ue.SSN,1,1) 
                    + SUBSTRING(ue.SSN,3,1)
                    + SUBSTRING(ue.SSN,5,1)
                    + SUBSTRING(ue.SSN,7,1)
                    + SUBSTRING(ue.SSN,9,1) UNION ALL
               SELECT SPACE(1) + SUBSTRING(ue.SSN,4,1) + SUBSTRING(ue.SSN,6,1) + SPACE(1) 
                    + SUBSTRING(ue.SSN,1,1) 
                    + SUBSTRING(ue.SSN,3,1)
                    + SUBSTRING(ue.SSN,5,1)
                    + SUBSTRING(ue.SSN,7,1)
                    + SUBSTRING(ue.SSN,9,1) UNION ALL
               SELECT SPACE(1) + SUBSTRING(ue.SSN,4,1) + SPACE(1) + SUBSTRING(ue.SSN,8,1) 
                    + SUBSTRING(ue.SSN,1,1) 
                    + SUBSTRING(ue.SSN,3,1)
                    + SUBSTRING(ue.SSN,5,1)
                    + SUBSTRING(ue.SSN,7,1)
                    + SUBSTRING(ue.SSN,9,1) UNION ALL
               SELECT SPACE(2) + SUBSTRING(ue.SSN,6,1) + SUBSTRING(ue.SSN,8,1)
                    + SUBSTRING(ue.SSN,1,1) 
                    + SUBSTRING(ue.SSN,3,1)
                    + SUBSTRING(ue.SSN,5,1)
                    + SUBSTRING(ue.SSN,7,1)
                    + SUBSTRING(ue.SSN,9,1) UNION ALL

               SELECT SUBSTRING(ue.SSN,2,1) 
                    + SUBSTRING(ue.SSN,4,1)
                    + SUBSTRING(ue.SSN,6,1)
                    + SUBSTRING(ue.SSN,8,1)
                    + SPACE(2) + SUBSTRING(ue.SSN,5,1) + SUBSTRING(ue.SSN,7,1) + SUBSTRING(ue.SSN,9,1) UNION ALL
               SELECT SUBSTRING(ue.SSN,2,1) 
                    + SUBSTRING(ue.SSN,4,1)
                    + SUBSTRING(ue.SSN,6,1)
                    + SUBSTRING(ue.SSN,8,1)
                    + SPACE(1) + SUBSTRING(ue.SSN,3,1) + SPACE(1) + SUBSTRING(ue.SSN,7,1) + SUBSTRING(ue.SSN,9,1) UNION ALL
               SELECT SUBSTRING(ue.SSN,2,1) 
                    + SUBSTRING(ue.SSN,4,1)
                    + SUBSTRING(ue.SSN,6,1)
                    + SUBSTRING(ue.SSN,8,1)
                    + SPACE(1) + SUBSTRING(ue.SSN,3,1) + SUBSTRING(ue.SSN,5,1) + SPACE(1) + SUBSTRING(ue.SSN,9,1) UNION ALL
               SELECT SUBSTRING(ue.SSN,2,1) 
                    + SUBSTRING(ue.SSN,4,1)
                    + SUBSTRING(ue.SSN,6,1)
                    + SUBSTRING(ue.SSN,8,1)
                    + SPACE(1) + SUBSTRING(ue.SSN,3,1) + SUBSTRING(ue.SSN,5,1) + SUBSTRING(ue.SSN,7,1) + SPACE(1) UNION ALL
               SELECT SUBSTRING(ue.SSN,2,1) 
                    + SUBSTRING(ue.SSN,4,1)
                    + SUBSTRING(ue.SSN,6,1)
                    + SUBSTRING(ue.SSN,8,1)
                    + SUBSTRING(ue.SSN,1,1) + SPACE(2) + SUBSTRING(ue.SSN,7,1) + SUBSTRING(ue.SSN,9,1) UNION ALL
               SELECT SUBSTRING(ue.SSN,2,1) 
                    + SUBSTRING(ue.SSN,4,1)
                    + SUBSTRING(ue.SSN,6,1)
                    + SUBSTRING(ue.SSN,8,1)
                    + SUBSTRING(ue.SSN,1,1) + SPACE(1) + SUBSTRING(ue.SSN,5,1) + SPACE(1) + SUBSTRING(ue.SSN,9,1) UNION ALL
               SELECT SUBSTRING(ue.SSN,2,1) 
                    + SUBSTRING(ue.SSN,4,1)
                    + SUBSTRING(ue.SSN,6,1)
                    + SUBSTRING(ue.SSN,8,1)
                    + SUBSTRING(ue.SSN,1,1) + SPACE(1) + SUBSTRING(ue.SSN,5,1) + SUBSTRING(ue.SSN,7,1) + SPACE(1) UNION ALL
               SELECT SUBSTRING(ue.SSN,2,1) 
                    + SUBSTRING(ue.SSN,4,1)
                    + SUBSTRING(ue.SSN,6,1)
                    + SUBSTRING(ue.SSN,8,1)
                    + SUBSTRING(ue.SSN,1,1) + SUBSTRING(ue.SSN,3,1) + SPACE(2) + SUBSTRING(ue.SSN,9,1) UNION ALL
               SELECT SUBSTRING(ue.SSN,2,1) 
                    + SUBSTRING(ue.SSN,4,1)
                    + SUBSTRING(ue.SSN,6,1)
                    + SUBSTRING(ue.SSN,8,1)
                    + SUBSTRING(ue.SSN,1,1) + SUBSTRING(ue.SSN,3,1) + SPACE(1) + SUBSTRING(ue.SSN,7,1) + SPACE(1) UNION ALL
               SELECT SUBSTRING(ue.SSN,2,1) 
                    + SUBSTRING(ue.SSN,4,1)
                    + SUBSTRING(ue.SSN,6,1)
                    + SUBSTRING(ue.SSN,8,1)
                    + SUBSTRING(ue.SSN,1,1) + SUBSTRING(ue.SSN,3,1) + SUBSTRING(ue.SSN,5,1) + SPACE(2))y
  WHERE v.SSN IS NULL)uen
  ON (val.SSN2 = uen.SSN2)
  GROUP BY Val.SSN,uen.SSN 
  UNION ALL
  SELECT  v.[SSN] , ue.SSN , 0
  FROM [dbo].[Validated] v 
  JOIN [dbo].[UserEntered] ue ON (ue.SSN = v.SSN)
ORDER BY [vSSN], [ueSSN], [status]

Answer 2

--Phil Factor 1A (I'll call this one the 'remove the hopeless and the obvious User-SSNS before you start'

--build up the DDL
--clear up if it crashed out with an error
if exists (select 1 from tempdb.information_schema.tables 
               where Table_Name like '#ValidSSN%')
    DROP TABLE #validSSN
if exists (select 1 from tempdb.information_schema.tables 
               where Table_Name like '#UserSSN%')
    DROP TABLE #UserSSN
if exists (select 1 from tempdb.information_schema.tables 
               where Table_Name like '#CheckedSSN%')
    DROP TABLE #CheckedSSN

/*we'll go for a temporary table since we need to exclude 
perfect matches from ambiguous matching. We may want to use 
it to remove duplicates if we are doing 'best matching'.*/
CREATE TABLE #CheckedSSN
  (
   vSSN CHAR(9) NOT NULL,
   ueSSN CHAR(9) NOT NULL,
   [Status] TINYINT NOT NULL
  )
--we put odd and even digits in separate strings for valid SSNs
CREATE TABLE #ValidSSN
  (
   Valid_ID INT IDENTITY(1, 1),
   SSN CHAR(9) NOT NULL primary key,
   evens AS (SUBSTRING(SSN, 2, 1)+SUBSTRING(SSN, 4, 1)+SUBSTRING(SSN, 6, 1)
             +SUBSTRING(SSN, 8, 1)) PERSISTED,
   odds AS (SUBSTRING(SSN, 1, 1)+SUBSTRING(SSN, 3, 1)+SUBSTRING(SSN, 5, 1)
            +SUBSTRING(SSN, 7, 1)+SUBSTRING(SSN, 9, 1)) PERSISTED
  )

--we put odd and even digits in separate strings for the ones the users enter
CREATE TABLE #UserSSN
  (
   UserSSN_ID INT IDENTITY(1, 1) primary key,
   SSN CHAR(9) NOT NULL,
   evens AS (SUBSTRING(SSN, 2, 1)+SUBSTRING(SSN, 4, 1)+SUBSTRING(SSN, 6, 1)
             +SUBSTRING(SSN, 8, 1)) PERSISTED,
   odds AS (SUBSTRING(SSN, 1, 1)+SUBSTRING(SSN, 3, 1)+SUBSTRING(SSN, 5, 1)
            +SUBSTRING(SSN, 7, 1)+SUBSTRING(SSN, 9, 1)) PERSISTED
  )

--do some work

--so we can slice up the strings and then index them
INSERT INTO #ValidSSN (SSN)
  SELECT [SSN] FROM Validated 

--we can remove the 0-error matches
INSERT INTO #CheckedSSN (vSSN, ueSSN, Status)
  SELECT Validated.SSN, UserEntered.SSN, 0
  FROM UserEntered
    INNER JOIN Validated
      ON Validated.SSN=UserEntered.SSN

--and we need only waste time on the dud matches
INSERT INTO #UserSSN (SSN)
  SELECT UserEntered.SSN
  FROM UserEntered
    LEFT OUTER JOIN Validated
      ON Validated.SSN=UserEntered.SSN
  WHERE
    Validated.SSN IS NULL--i.e. where we didnt get a match

CREATE INDEX idxValidEvenAndOdd ON #ValidSSN(evens,Odds)
CREATE INDEX idxUsersEvenAndOdd ON #UserSSN(evens,Odds)

--pop in the 1-error matches   where it was an error on the odd side only
INSERT INTO #CheckedSSN (vSSN, ueSSN, Status)
SELECT #ValidSSN.SSN, #UserSSN.SSN, 1 FROM #ValidSSN
    INNER JOIN #UserSSN
    ON #ValidSSN.evens=#UserSSN.evens
WHERE CASE WHEN SUBSTRING(#ValidSSN.odds,1,1)
                 =SUBSTRING(#UserSSN.odds,1,1) THEN 1 ELSE 0 END+    
CASE WHEN SUBSTRING(#ValidSSN.odds,2,1)
                 =SUBSTRING(#UserSSN.odds,2,1) THEN 1 ELSE 0 END+ 
CASE WHEN SUBSTRING(#ValidSSN.odds,3,1)
                 =SUBSTRING(#UserSSN.odds,3,1) THEN 1 ELSE 0 END+ 
CASE WHEN SUBSTRING(#ValidSSN.odds,4,1)
                 =SUBSTRING(#UserSSN.odds,4,1) THEN 1 ELSE 0 END+ 
CASE WHEN SUBSTRING(#ValidSSN.odds,5,1)
                 =SUBSTRING(#UserSSN.odds,5,1) THEN 1 ELSE 0 END
=4
--pop in the 1-error matches   where it was an error on the even side only
INSERT INTO #CheckedSSN (vSSN, ueSSN, Status)
SELECT #ValidSSN.SSN, #UserSSN.SSN, 1 FROM #ValidSSN
    INNER JOIN #UserSSN
    ON #ValidSSN.Odds=#UserSSN.Odds
WHERE CASE WHEN SUBSTRING(#ValidSSN.evens,1,1)
                 =SUBSTRING(#UserSSN.evens,1,1) THEN 1 ELSE 0 END+    
CASE WHEN SUBSTRING(#ValidSSN.evens,2,1)
                 =SUBSTRING(#UserSSN.evens,2,1) THEN 1 ELSE 0 END+ 
CASE WHEN SUBSTRING(#ValidSSN.evens,3,1)
                 =SUBSTRING(#UserSSN.evens,3,1) THEN 1 ELSE 0 END+ 
CASE WHEN SUBSTRING(#ValidSSN.evens,4,1)
                 =SUBSTRING(#UserSSN.evens,4,1) THEN 1 ELSE 0 END 
=3   
--pop in the 2-error matches (where no perfect match exists) 
-- it was an error on the odd side only
INSERT INTO #CheckedSSN (vSSN, ueSSN, Status)
SELECT #ValidSSN.SSN, #UserSSN.SSN, 2 FROM #ValidSSN
    INNER JOIN #UserSSN
    ON #ValidSSN.evens=#UserSSN.evens
    left outer join #CheckedSSN on #UserSSN.SSN =#CheckedSSN.ueSSN and status=0
    WHERE CASE WHEN SUBSTRING(#ValidSSN.odds,1,1)
                   =SUBSTRING(#UserSSN.odds,1,1) THEN 1 ELSE 0 END+    
CASE WHEN SUBSTRING(#ValidSSN.odds,2,1)
                  =SUBSTRING(#UserSSN.odds,2,1) THEN 1 ELSE 0 END+ 
CASE WHEN SUBSTRING(#ValidSSN.odds,3,1)
                  =SUBSTRING(#UserSSN.odds,3,1) THEN 1 ELSE 0 END+ 
CASE WHEN SUBSTRING(#ValidSSN.odds,4,1)
                  =SUBSTRING(#UserSSN.odds,4,1) THEN 1 ELSE 0 END+ 
CASE WHEN SUBSTRING(#ValidSSN.odds,5,1)
                  =SUBSTRING(#UserSSN.odds,5,1) THEN 1 ELSE 0 END
=3
and #CheckedSSN.ueSSN is null
--pop in the 2-error matches (where no perfect match exists) 
--where it was an error on the even side only
INSERT INTO #CheckedSSN (vSSN, ueSSN, Status)
SELECT #ValidSSN.SSN, #UserSSN.SSN, 2  FROM #ValidSSN
    INNER JOIN #UserSSN
    ON #ValidSSN.Odds=#UserSSN.Odds
    left outer join #CheckedSSN on #UserSSN.SSN =#CheckedSSN.ueSSN and status=0
--the outer join is to eliminate those that had perfect matched    
WHERE CASE WHEN SUBSTRING(#ValidSSN.evens,1,1)
                   =SUBSTRING(#UserSSN.evens,1,1) THEN 1 ELSE 0 END+    
CASE WHEN SUBSTRING(#ValidSSN.evens,2,1)
                   =SUBSTRING(#UserSSN.evens,2,1) THEN 1 ELSE 0 END+ 
CASE WHEN SUBSTRING(#ValidSSN.evens,3,1)
                   =SUBSTRING(#UserSSN.evens,3,1) THEN 1 ELSE 0 END+ 
CASE WHEN SUBSTRING(#ValidSSN.evens,4,1)
                   =SUBSTRING(#UserSSN.evens,4,1) THEN 1 ELSE 0 END 
=2   
and #CheckedSSN.ueSSN is null
--print out the results
Select vSSN,
   ueSSN,
   [Status] from #checkedSSN order by vSSN, ueSSN, Status
---clean up
DROP TABLE #validSSN
DROP TABLE #UserSSN
DROP TABLE #CheckedSSN

Answer 3

I call this the pure SQL CLR solution... J

select * from dbo.match()

Oh, by the way you need to add the SQL CLR assembly first...

CREATE ASSEMBLY [SSNMatch]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103008E81F24B0000000000000000E00002210B010800001800000006000000000000EE3700000020000000400000000040000020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000009C3700004F000000004000008003000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000F4170000002000000018000000020000000000000000000000000000200000602E72737263000000800300000040000000040000001A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001E00000000000000000000000000004000004200000000000000000000000000000000D03700000000000048000000020005001C290000800E000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B30040053050000010000117201000070730F00000A130411046F1000000A72310000701104731100000A130511056F1200000AA518000001130611068D180000010A1106731300000A0C1106731300000A0BDE0C11052C0711056F1400000ADC72770000701104731100000A130711076F1200000AA518000001130811088D180000010DDE0C11072C0711076F1400000ADC72C10000701104731100000A130911096F1500000A130A16130B389E000000110A166F1600000A741E000001281700000A130C06110B110C9E110C2804000006130D110C2805000006130E07110D6F1800000A2C1107110D6F1900000A110B6F1A00000A2B1B1C731B00000A130F110F110B6F1A00000A07110D110F6F1C00000A08110E6F1800000A2C1108110E6F1900000A110B6F1A00000A2B1B1C731B00000A13101110110B6F1A00000A08110E11106F1C00000A110B1758130B110A6F1D00000A3A56FFFFFFDE0C110A2C07110A6F1400000ADCDE0C11092C0711096F1400000ADC72010100701104731100000A131111116F1500000A13121613132B1B09111325175813131112166F1600000A741E000001281700000A9E11126F1D00000A2DDCDE0C11122C0711126F1400000ADCDE0C11112C0711116F1400000ADCDE0C11042C0711046F1400000ADC2000CA9A3B731E00000A13140613271613282B171127112894131511141115176F1F00000A112817581328112811278E6932E120A0860100731E00000A1316086F2000000A6F2100000A13292B1311296F2200000A131711161117176F1F00000A11296F2300000A2DE4DE0C11292C0711296F1400000ADC20A0860100731E00000A1318076F2000000A6F2100000A132A2B13112A6F2200000A131911181119176F1F00000A112A6F2300000A2DE4DE0C112A2C07112A6F1400000ADC098E69732400000A131A09132B16132C3898020000112B112C94131B1114111B6F2500000A2C43111A730900000613261126121B7245010070282600000A7D010000041126121B7245010070282600000A7D020000041126167D0300000411266F2700000A383D020000111B2804000006131C111B2805000006131D1118111C6F2500000A39F600000007111C6F1900000A6F2800000A132D38C6000000122D282900000A131E06111E94131F111F1F0A5B1F0A5D111D1F0A5D3303162B011713201120111F20E80300005B1F0A5D111D1F0A5B1F0A5D3303162B01175813201120111F20A08601005B1F0A5D111D1F645B1F0A5D3303162B01175813201120111F20809698005B1F0A5D111D20E80300005B1F0A5D3303162B01175813201120183040111A730900000613211121121F7245010070282600000A7D010000041121121B7245010070282600000A7D0200000411211120D27D0300000411216F2700000A122D282A00000A3A2EFFFFFFDE0E122DFE160600001B6F1400000ADC1116111D6F2500000A391901000008111D6F1900000A6F2800000A132E38E9000000122E282900000A1322061122941323112328040000062611231F0A5D111C1F0A5D3303162B01171324112411231F645B1F0A5D111C1F0A5B1F0A5D3303162B01175813241124112320102700005B1F0A5D111C1F645B1F0A5D3303162B0117581324112411232040420F005B1F0A5D111C20E80300005B1F0A5D3303162B0117581324112411232000E1F5055B1F0A5D111C20102700005B1F0A5D3303162B01175813241124183040111A73090000061325112512237245010070282600000A7D010000041125121B7245010070282600000A7D0200000411251124D27D0300000411256F2700000A122E282A00000A3A0BFFFFFFDE0E122EFE160600001B6F1400000ADC112C1758132C112C112B8E693F5DFDFFFF111A6F2B00000A2A00410C0100020000002100000028000000490000000C000000000000000200000063000000180000007B0000000C00000000000000020000009E000000B4000000520100000C000000000000000200000095000000CB000000600100000C0000000000000002000000830100002B000000AE0100000C00000000000000020000007A01000042000000BC0100000C00000000000000020000000C000000BE010000CA0100000C00000000000000020000002202000020000000420200000C00000000000000020000006702000020000000870200000C00000000000000020000002C030000D9000000050400000E000000000000000200000030040000FC0000002C0500000E00000000000000133004006800000002000011160A028E6917590B2B4C03020694596C020794020694596C5B0D060706596C095A23000000000000E03F5869580C0816320608028E692626020894033302082A0208940331060817590B2B0A020894032F040817580A06072F0C020694033006020794032FA4152A133003006000000003000011160A028E6917590B2B44060706596C23000000000000E03F5A23000000000000E03F5869580C0816320608028E692626020894033302082A0208940331060817590B2B0A020894032F040817580A06072F0C020694033006020794032FAC152A033003004000000000000000021F0A5D021F645B1F0A5D1F0A5A580220102700005B1F0A5D1F645A58022040420F005B1F0A5D20E80300005A58022000E1F5055B1F0A5D20102700005A582AD6021F0A5B1F0A5D0220E80300005B1F0A5D1F0A5A580220A08601005B1F0A5D1F645A580220809698005B1F0A5D20E80300005A582A0000010000000A00000064000000E803000010270000A086010040420F008096980000E1F50513300400700000000400001120A0860100731E00000A0A1F098D1800000125D004000004282D00000A0B160C2B48170D2B3B08175813042B2B1713052B1A06070894095A0711049411055A58176F1F00000A11051758130511051F0931E011041758130411041B32D00917580D091F0931C00817580C081B32B4062A133003003D000000050000110274030000020A03067B01000004282E00000A732F00000A5104067B02000004282E00000A732F00000A5105067B03000004283000000A81050000012A1E02283100000A2A1E02283100000A2A00000042534A4201000100000000000C00000076322E302E35303732370000000005006C00000060040000237E0000CC0400005805000023537472696E677300000000240A00005001000023555300740B0000100000002347554944000000840B0000FC02000023426C6F620000000000000002000001579502280902000000FA253300160000010000002A0000000500000004000000090000000A000000310000000C0000000100000005000000060000000100000001000000030000000200000000000A00010000000000060046003F00060060004D00060091004D000A00C800B3000A00D100B30006002801090106004701350106005E01350106007B01350106009A0135010600B30135010600CC0135010600E701350106000202350106001B02090106002F0235010600680248020600880248020A00CA02AF020A00F502DF020A00160303030A002803DF020A003303030306004B033F000E006C035103060079035103060080033F000A009403DF020A00B00303030600C6033F000600CD033F000600F603510306000704510306001504510306003D044D006B005B0400000600B30448020600CE043F00060009054802060018053F0006001E053F000A004105B30000000000010000000000010001000100100017000000050001000100050010002C000000050001000900000000006E040000050004000A0013010000D8040000990005000A000600E40032000600E90032000600EF0035001301F5045E0250200000000096006C000A000100BC2600000000910072000F00010030270000000091007A000F0003009C27000000009100820016000500E8270000000091008A001600060044280000000091009A001B000700C028000000009600D900200007000929000000008618DE002E000B001129000000008618DE002E000B0000000100F60000000200FA0000000100F60000000200FA0000000100FE0000000100FE0000000100020102000200E40002000300E90002000400EF003100DE002E003900DE0038004100DE0038004900DE0038005100DE0038005900DE0038006100DE0038006900DE0038007100DE0038007900DE003D008100DE0038008900DE0042009100DE002E009900DE002E00A100DE003800A90023032E00B100DE003701B9003D033E010C00DE004200D9008C032E00B100A2034D01E900BD035201F900D50357010C00DD035C010C00BD0362011400E9036F011400DE0042000C00E9037501E900ED037D011900DE0042001900F20381010C00FE0387011C002304980124003104A901190149047D012C00DE0042001900BD03B501C1005204BA012C00E9036F0114002304BF0134003104A901340049047D012C006604D0012901DE002E0039013105620251014B0577022100DE007E0229004B0585020900DE002E002000730047002E002B00A4022E00130090022E001B009E022E0023009E022E0043009E022E006B00DD022E003B00B3022E00330090022E0053009E022E006300D4028300630159020100240000000500D6014C0253026C028B02420169019101A201AE01C9012028000004000480000001000000CE0E8F5B000000000000A602000002000000000000000000000001003600000000000200000000000000000000000100A7000000000002000000000000000000000001003F000000000003000200050004000000003C4D6F64756C653E0053534E4D617463682E646C6C0055736572446566696E656446756E6374696F6E7300526573756C74526F77006D73636F726C69620053797374656D004F626A6563740053797374656D2E436F6C6C656374696F6E730049456E756D657261626C65004D6174636800465365617263680042536561726368004765744576656E004765744F646400426974417272617900416C6C6F77656444696666730053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C43686172730053716C42797465004E657874002E63746F72007653534E00756553534E00537461747573006172720073736E006E756D006F626A526F770053797374656D2E52756E74696D652E496E7465726F705365727669636573004F75744174747269627574650053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053534E4D61746368004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E4174747269627574650053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E0053716C436F6D6D616E64004462436F6D6D616E6400457865637574655363616C617200496E7433320053797374656D2E436F6C6C656374696F6E732E47656E6572696300536F727465644C6973746032004C69737460310049446973706F7361626C6500446973706F73650053716C44617461526561646572004578656375746552656164657200446244617461526561646572006765745F4974656D00537472696E6700436F6E7665727400546F496E74333200436F6E7461696E734B65790041646400526561640053657400494C6973746031006765745F4B6579730049456E756D657261626C6560310049456E756D657261746F72603100476574456E756D657261746F72006765745F43757272656E740049456E756D657261746F72004D6F76654E65787400546F537472696E6700456E756D657261746F7200546F4172726179003C50726976617465496D706C656D656E746174696F6E44657461696C733E7B39454630363145462D453334352D343442342D414431452D3635304637383231393645457D00436F6D70696C657247656E6572617465644174747269627574650056616C756554797065005F5F5374617469634172726179496E69745479706553697A653D33360024246D6574686F643078363030303030362D310052756E74696D6548656C706572730041727261790052756E74696D654669656C6448616E646C6500496E697469616C697A6541727261790053716C537472696E67006F705F496D706C696369740000002F63006F006E007400650078007400200063006F006E006E0065006300740069006F006E003D0074007200750065000045530045004C00450043005400200043004F0055004E00540028002A0029002000460052004F004D002000640062006F002E00560061006C006900640061007400650064000049530045004C00450043005400200043004F0055004E00540028002A0029002000460052004F004D002000640062006F002E00550073006500720045006E0074006500720065006400003F530045004C0045004300540020005B00530053004E005D002000460052004F004D002000640062006F002E00560061006C006900640061007400650064000043530045004C0045004300540020005B00530053004E005D002000460052004F004D002000640062006F002E00550073006500720045006E0074006500720065006400000764003000390000000000EF61F09E45E3B444AD1E650F782196EE0008B77A5C561934E0890400001209060002081D08080400010808040000120D0D0004011C1012111012111011150320000102060E020605042001010E0420010102042001010880EE010003005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A4461746141636365737301000000540E0F5461626C65446566696E6974696F6E335B7653534E5D206E636861722839292C205B756553534E5D206E636861722839292C205B5374617475735D2074696E79696E74540E1146696C6C526F774D6574686F644E616D65044E657874062002010E12510320001C0A1512650208151269010804200012710420011C08040001080E052001021300062001130113000515126901080520010113000720020113001301032000020520020108020920001512808101130006151280850108092000151280890113000615128089010804200013000615126901120C04200102080420010E0E09200015118091011300061511809101080520001D130075072F1D0815126502081512690108151265020815126901081D0812511259081259081259127108080808151269010815126901081259127108120D08120D08120D0815126901120C080808080808120C080808120C120C1D08081512808901081512808901081D08081511809101081511809101080607040808080D050703080808040100000003061114090002011280A11180A50A0706120D1D08080808080600011180A90E062001011180A9050001111505040701120C0D01000853534E4D6174636800000501000000000E0100094D6963726F736F667400002001001B436F7079726967687420C2A9204D6963726F736F6674203230313000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301C43700000000000000000000DE370000002000000000000000000000000000000000000000000000D0370000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000280300000000000000000000280334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001008F5BCE0E000001008F5BCE0E3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00488020000010053007400720069006E006700460069006C00650049006E0066006F00000064020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F006600740000003C0009000100460069006C0065004400650073006300720069007000740069006F006E0000000000530053004E004D0061007400630068000000000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0033003700390030002E0032003300340033003900000000003C000D00010049006E007400650072006E0061006C004E0061006D0065000000530053004E004D0061007400630068002E0064006C006C00000000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F0066007400200032003000310030000000000044000D0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530053004E004D0061007400630068002E0064006C006C0000000000340009000100500072006F0064007500630074004E0061006D00650000000000530053004E004D0061007400630068000000000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033003700390030002E00320033003400330039000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033003700390030002E0032003300340033003900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000F03700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE

GO

CREATE FUNCTION [dbo].[Match]()
RETURNS  TABLE (
    [vSSN] [nchar](9) NULL,
    [ueSSN] [nchar](9) NULL,
    [Status] [tinyint] NULL
) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [SSNMatch].[UserDefinedFunctions].[Match]
GO

Answer 4

New version (more memory conservative, but slower). I call this the pure SQL CLR solution 2... J

select * from dbo.match()

Code for the SQL CLR assembly...

CREATE ASSEMBLY [SSNMatch]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030030E8F24B0000000000000000E00002210B010800001800000006000000000000BE3700000020000000400000000040000020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000006837000053000000004000008003000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000C4170000002000000018000000020000000000000000000000000000200000602E72737263000000800300000040000000040000001A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001E00000000000000000000000000004000004200000000000000000000000000000000A0370000000000004800000002000500EC2800007C0E000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B30040020050000010000117201000070730F00000A130411046F1000000A72310000701104731100000A130511056F1200000AA518000001130611068D180000010A1106731300000A0C1106731300000A0BDE0C11052C0711056F1400000ADC72770000701104731100000A130711076F1200000AA518000001130811088D180000010DDE0C11072C0711076F1400000ADC72C10000701104731100000A130911096F1500000A130A16130B389E000000110A166F1600000A741E000001281700000A130C06110B110C9E110C2804000006130D110C2805000006130E07110D6F1800000A2C1107110D6F1900000A110B6F1A00000A2B1B1C731B00000A130F110F110B6F1A00000A07110D110F6F1C00000A08110E6F1800000A2C1108110E6F1900000A110B6F1A00000A2B1B1C731B00000A13101110110B6F1A00000A08110E11106F1C00000A110B1758130B110A6F1D00000A3A56FFFFFFDE0C110A2C07110A6F1400000ADCDE0C11092C0711096F1400000ADC72010100701104731100000A131111116F1500000A13121613132B1B09111325175813131112166F1600000A741E000001281700000A9E11126F1D00000A2DDCDE0C11122C0711126F1400000ADCDE0C11112C0711116F1400000ADCDE0C11042C0711046F1400000ADC20A0860100731E00000A1314086F1F00000A6F2000000A13252B1311256F2100000A131511141115176F2200000A11256F2300000A2DE4DE0C11252C0711256F1400000ADC20A0860100731E00000A1316076F1F00000A6F2000000A13262B1311266F2100000A131711161117176F2200000A11266F2300000A2DE4DE0C11262C0711266F1400000ADC098E69732400000A13180913271613283898020000112711289413190611192802000006152E43111873090000061324112412197245010070282500000A7D01000004112412197245010070282500000A7D020000041124167D0300000411246F2600000A383D02000011192804000006131A11192805000006131B1116111A6F2700000A39F600000007111A6F1900000A6F2800000A132938C60000001229282900000A131C06111C94131D111D1F0A5B1F0A5D111B1F0A5D3303162B0117131E111E111D20E80300005B1F0A5D111B1F0A5B1F0A5D3303162B011758131E111E111D20A08601005B1F0A5D111B1F645B1F0A5D3303162B011758131E111E111D20809698005B1F0A5D111B20E80300005B1F0A5D3303162B011758131E111E18304011187309000006131F111F121D7245010070282500000A7D01000004111F12197245010070282500000A7D02000004111F111ED27D03000004111F6F2600000A1229282A00000A3A2EFFFFFFDE0E1229FE160600001B6F1400000ADC1114111B6F2700000A391901000008111B6F1900000A6F2800000A132A38E9000000122A282900000A1320061120941321112128040000062611211F0A5D111A1F0A5D3303162B01171322112211211F645B1F0A5D111A1F0A5B1F0A5D3303162B01175813221122112120102700005B1F0A5D111A1F645B1F0A5D3303162B0117581322112211212040420F005B1F0A5D111A20E80300005B1F0A5D3303162B0117581322112211212000E1F5055B1F0A5D111A20102700005B1F0A5D3303162B01175813221122183040111873090000061323112312217245010070282500000A7D01000004112312197245010070282500000A7D0200000411231122D27D0300000411236F2600000A122A282A00000A3A0BFFFFFFDE0E122AFE160600001B6F1400000ADC112817581328112811278E693F5DFDFFFF11186F2B00000A2A410C0100020000002100000028000000490000000C000000000000000200000063000000180000007B0000000C00000000000000020000009E000000B4000000520100000C000000000000000200000095000000CB000000600100000C0000000000000002000000830100002B000000AE0100000C00000000000000020000007A01000042000000BC0100000C00000000000000020000000C000000BE010000CA0100000C0000000000000002000000EF010000200000000F0200000C00000000000000020000003402000020000000540200000C0000000000000002000000F9020000D9000000D20300000E0000000000000002000000FD030000FC000000F90400000E00000000000000133004006800000002000011160A028E6917590B2B4C03020694596C020794020694596C5B0D060706596C095A23000000000000E03F5869580C0816320608028E692626020894033302082A0208940331060817590B2B0A020894032F040817580A06072F0C020694033006020794032FA4152A133003006000000003000011160A028E6917590B2B44060706596C23000000000000E03F5A23000000000000E03F5869580C0816320608028E692626020894033302082A0208940331060817590B2B0A020894032F040817580A06072F0C020694033006020794032FAC152A033003004000000000000000021F0A5D021F645B1F0A5D1F0A5A580220102700005B1F0A5D1F645A58022040420F005B1F0A5D20E80300005A58022000E1F5055B1F0A5D20102700005A582AD6021F0A5B1F0A5D0220E80300005B1F0A5D1F0A5A580220A08601005B1F0A5D1F645A580220809698005B1F0A5D20E80300005A582A000000000000010000000A00000064000000E803000010270000A086010040420F008096980000E1F50513300400700000000400001120A0860100731E00000A0A1F098D1800000125D004000004282D00000A0B160C2B48170D2B3B08175813042B2B1713052B1A06070894095A0711049411055A58176F2200000A11051758130511051F0931E011041758130411041B32D00917580D091F0931C00817580C081B32B4062A133003003D000000050000110274030000020A03067B01000004282E00000A732F00000A5104067B02000004282E00000A732F00000A5105067B03000004283000000A81050000012A1E02283100000A2A1E02283100000A2A00000042534A4201000100000000000C00000076322E302E35303732370000000005006C00000060040000237E0000CC0400005805000023537472696E677300000000240A00005001000023555300740B0000100000002347554944000000840B0000F802000023426C6F620000000000000002000001579502280902000000FA253300160000010000002A0000000500000004000000090000000A000000310000000C0000000100000005000000060000000100000001000000030000000200000000000A00010000000000060046003F00060060004D00060091004D000A00C800B3000A00D100B30006002801090106004701350106005E01350106007B01350106009A0135010600B30135010600CC0135010600E701350106000202350106001B02090106002F0235010600680248020600880248020A00CA02AF020A00F502DF020A00160303030A002803DF020A003303030306004B033F000E006C035103060079035103060080033F000A009403DF020A00B00303030600C6033F000600CD033F000600F203510306000304510306001104510306003D044D006B005B0400000600B30448020600CE043F00060009054802060018053F0006001E053F000A004105B30000000000010000000000010001000100100017000000050001000100050010002C000000050001000900000000006E040000050004000A0013010000D8040000990005000A000600E40032000600E90032000600EF0035001301F504580250200000000096006C000A000100882600000000910072000F000100FC260000000091007A000F0003006827000000009100820016000500B4270000000091008A001600060014280000000091009A001B0007009028000000009600D90020000700D928000000008618DE002E000B00E128000000008618DE002E000B0000000100F60000000200FA0000000100F60000000200FA0000000100FE0000000100FE0000000100020102000200E40002000300E90002000400EF003100DE002E003900DE0038004100DE0038004900DE0038005100DE0038005900DE0038006100DE0038006900DE0038007100DE0038007900DE003D008100DE0038008900DE0042009100DE002E009900DE002E00A100DE003800A90023032E00B100DE003701B9003D033E010C00DE004200D9008C032E00B100A2034D01E900BD035201F900D50357010C00DD035C010C00BD0362011400E9036F011400DE0042000C00E9037501E900ED037D011900DE0042000C00FA0381011C001F04920124002D04A30119003904A801190149047D012C00DE004200C1005204B5012C00E9036F011900BD03BA0114001F04BF0134002D04A301340049047D012C006604D0012901DE002E00390131055C0251014B0571022100DE00780229004B057F020900DE002E002000730047002E002B009E022E0013008A022E001B0098022E00230098022E00430098022E006B00D7022E003B00AD022E0033008A022E00530098022E006300CE028300630153020100240000000500D60146024D0266028502420169018B019C01AE01C901F027000004000480000001000000CE0EE08E000000000000A602000002000000000000000000000001003600000000000200000000000000000000000100A7000000000002000000000000000000000001003F000000000003000200050004000000003C4D6F64756C653E0053534E4D617463682E646C6C0055736572446566696E656446756E6374696F6E7300526573756C74526F77006D73636F726C69620053797374656D004F626A6563740053797374656D2E436F6C6C656374696F6E730049456E756D657261626C65004D6174636800465365617263680042536561726368004765744576656E004765744F646400426974417272617900416C6C6F77656444696666730053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C43686172730053716C42797465004E657874002E63746F72007653534E00756553534E00537461747573006172720073736E006E756D006F626A526F770053797374656D2E52756E74696D652E496E7465726F705365727669636573004F75744174747269627574650053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053534E4D61746368004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E4174747269627574650053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E0053716C436F6D6D616E64004462436F6D6D616E6400457865637574655363616C617200496E7433320053797374656D2E436F6C6C656374696F6E732E47656E6572696300536F727465644C6973746032004C69737460310049446973706F7361626C6500446973706F73650053716C44617461526561646572004578656375746552656164657200446244617461526561646572006765745F4974656D00537472696E6700436F6E7665727400546F496E74333200436F6E7461696E734B657900416464005265616400494C6973746031006765745F4B6579730049456E756D657261626C6560310049456E756D657261746F72603100476574456E756D657261746F72006765745F43757272656E74005365740049456E756D657261746F72004D6F76654E65787400546F537472696E6700456E756D657261746F7200546F4172726179003C50726976617465496D706C656D656E746174696F6E44657461696C733E7B46394443393744462D324146382D344135462D394144382D3836384530354646323137467D00436F6D70696C657247656E6572617465644174747269627574650056616C756554797065005F5F5374617469634172726179496E69745479706553697A653D33360024246D6574686F643078363030303030362D310052756E74696D6548656C706572730041727261790052756E74696D654669656C6448616E646C6500496E697469616C697A6541727261790053716C537472696E67006F705F496D706C696369740000002F63006F006E007400650078007400200063006F006E006E0065006300740069006F006E003D0074007200750065000045530045004C00450043005400200043004F0055004E00540028002A0029002000460052004F004D002000640062006F002E00560061006C006900640061007400650064000049530045004C00450043005400200043004F0055004E00540028002A0029002000460052004F004D002000640062006F002E00550073006500720045006E0074006500720065006400003F530045004C0045004300540020005B00530053004E005D002000460052004F004D002000640062006F002E00560061006C006900640061007400650064000043530045004C0045004300540020005B00530053004E005D002000460052004F004D002000640062006F002E00550073006500720045006E0074006500720065006400000764003000390000000000DF97DCF9F82A5F4A9AD8868E05FF217F0008B77A5C561934E0890400001209060002081D08080400010808040000120D0D0004011C1012111012111011150320000102060E020605042001010E0420010102042001010880EE010003005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A4461746141636365737301000000540E0F5461626C65446566696E6974696F6E335B7653534E5D206E636861722839292C205B756553534E5D206E636861722839292C205B5374617475735D2074696E79696E74540E1146696C6C526F774D6574686F644E616D65044E657874062002010E12510320001C0A1512650208151269010804200012710420011C08040001080E052001021300062001130113000515126901080520010113000720020113001301032000020920001512808101130006151280850108092000151280890113000615128089010804200013000520020108020615126901120C0420010E0E042001020809200015118091011300061511809101080520001D13006F072B1D0815126502081512690108151265020815126901081D0812511259081259081259127108080808151269010815126901081259127108120D08120D0815126901120C080808080808120C080808120C120C1512808901081512808901081D08081511809101081511809101080607040808080D050703080808040100000003061114090002011280A11180A50A0706120D1D08080808080600011180A90E062001011180A9050001111505040701120C0D01000853534E4D6174636800000501000000000E0100094D6963726F736F667400002001001B436F7079726967687420C2A9204D6963726F736F6674203230313000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000903700000000000000000000AE370000002000000000000000000000000000000000000000000000A037000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000280300000000000000000000280334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100E08ECE0E00000100E08ECE0E3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00488020000010053007400720069006E006700460069006C00650049006E0066006F00000064020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F006600740000003C0009000100460069006C0065004400650073006300720069007000740069006F006E0000000000530053004E004D0061007400630068000000000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0033003700390030002E0033003600350037003600000000003C000D00010049006E007400650072006E0061006C004E0061006D0065000000530053004E004D0061007400630068002E0064006C006C00000000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F0066007400200032003000310030000000000044000D0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530053004E004D0061007400630068002E0064006C006C0000000000340009000100500072006F0064007500630074004E0061006D00650000000000530053004E004D0061007400630068000000000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033003700390030002E00330036003500370036000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033003700390030002E0033003600350037003600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000C03700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE

GO

CREATE FUNCTION [dbo].[Match]()
RETURNS  TABLE (
    [vSSN] [nchar](9) NULL,
    [ueSSN] [nchar](9) NULL,
    [Status] [tinyint] NULL
) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [SSNMatch].[UserDefinedFunctions].[Match]
GO

Answer 5

Matt v1

Titled the 'This probably really won't scale very well, but it's fast for the given data' version

DECLARE @UserEnteredTmp TABLE (
    [u1] [tinyint] NOT NULL, [u2] [tinyint] NOT NULL, [u3] [tinyint] NOT NULL, [u4] [tinyint] NOT NULL, [u5] [tinyint] NOT NULL,
    [u6] [tinyint] NOT NULL, [u7] [tinyint] NOT NULL, [u8] [tinyint] NOT NULL, [u9] [tinyint] NOT NULL, [odds] [int] NOT NULL,
    [evens] [smallint] NOT NULL, [ssn] [char] (9) )
DECLARE @ValidatedTmp TABLE (
    [u1] [tinyint] NOT NULL, [u2] [tinyint] NOT NULL, [u3] [tinyint] NOT NULL, [u4] [tinyint] NOT NULL, [u5] [tinyint] NOT NULL,
    [u6] [tinyint] NOT NULL, [u7] [tinyint] NOT NULL, [u8] [tinyint] NOT NULL, [u9] [tinyint] NOT NULL, [odds] [int] NOT NULL,
    [evens] [smallint] NOT NULL, [ssn] [char] (9) )

INSERT INTO @UserEnteredTmp ([u1], [u2], [u3], [u4], [u5], [u6], [u7], [u8], [u9], [odds], [evens], [ssn])
SELECT [u1], [u2], [u3], [u4], [u5], [u6], [u7], [u8], [u9], [u1] + [u3] + [u5] + [u7] + [u9], [u2] + [u4] + [u6] + [u8], [SSN] FROM 
(
       SELECT SUBSTRING(SSN, 1, 1) AS u1, SUBSTRING(SSN, 2, 1) AS u2, SUBSTRING(SSN, 3, 1) AS u3, SUBSTRING(SSN, 4, 1) AS u4,
              SUBSTRING(SSN, 5, 1) AS u5, SUBSTRING(SSN, 6, 1) AS u6, SUBSTRING(SSN, 7, 1) AS u7, SUBSTRING(SSN, 8, 1) AS u8,
              SUBSTRING(SSN, 9, 1) AS u9, [SSN]
       FROM  (SELECT [SSN] AS SSN 
                FROM [dbo].[UserEntered] 
               WHERE [SSN] NOT IN 
                    (SELECT [SSN] 
                       FROM [dbo].[Validated])
              ) idat
) odat

INSERT INTO @ValidatedTmp ([u1], [u2], [u3], [u4], [u5], [u6], [u7], [u8], [u9], [odds], [evens], [ssn])
SELECT [u1], [u2], [u3], [u4], [u5], [u6], [u7], [u8], [u9], [u1] + [u3] + [u5] + [u7] + [u9], [u2] + [u4] + [u6] + [u8], [ssn] FROM 
(
       SELECT SUBSTRING(SSN, 1, 1) AS u1, SUBSTRING(SSN, 2, 1) AS u2, SUBSTRING(SSN, 3, 1) AS u3, SUBSTRING(SSN, 4, 1) AS u4,
              SUBSTRING(SSN, 5, 1) AS u5, SUBSTRING(SSN, 6, 1) AS u6, SUBSTRING(SSN, 7, 1) AS u7, SUBSTRING(SSN, 8, 1) AS u8,
              SUBSTRING(SSN, 9, 1) AS u9, [SSN]
         FROM [dbo].[Validated]
) odat

SELECT   [vSSN], [ueSSN], [Status]
FROM     (SELECT [SSN] AS ueSSN, [SSN] AS vSSN, 0 AS [Status]
          FROM   (SELECT [dbo].[UserEntered].[SSN] AS SSN
                  FROM   [dbo].[UserEntered]
                  WHERE  [SSN] IN (SELECT [SSN]
                                   FROM   [dbo].[Validated])) AS idat
          UNION ALL
          SELECT [ueSSN], [vSSN], [Status]
          FROM   (SELECT [UserEnteredTmp].[ssn] AS ueSSN, [ValidatedTmp].[ssn] AS vSSN,
                         CASE WHEN [ValidatedTmp].[u2] = [UserEnteredTmp].[u2] THEN 0 ELSE 1 END + CASE WHEN [ValidatedTmp].[u4] = [UserEnteredTmp].[u4] THEN 0 ELSE 1 END + CASE WHEN [ValidatedTmp].[u6] = [UserEnteredTmp].[u6] THEN 0 ELSE 1 END + CASE WHEN [ValidatedTmp].[u8] = [UserEnteredTmp].[u8] THEN 0 ELSE 1 END AS [Status]
                  FROM   @UserEnteredTmp AS UserEnteredTmp INNER JOIN
                         @ValidatedTmp AS ValidatedTmp ON [UserEnteredTmp].[odds] = [ValidatedTmp].[odds]) AS idat
          WHERE  [Status] < 3
          UNION ALL
          SELECT [ueSSN], [vSSN], [Status]
          FROM   (SELECT [UserEnteredTmp].[ssn] AS ueSSN, [ValidatedTmp].[ssn] AS vSSN,
                         CASE WHEN [ValidatedTmp].[u1] = [UserEnteredTmp].[u1] THEN 0 ELSE 1 END + CASE WHEN [ValidatedTmp].[u3] = [UserEnteredTmp].[u3] THEN 0 ELSE 1 END + CASE WHEN [ValidatedTmp].[u5] = [UserEnteredTmp].[u5] THEN 0 ELSE 1 END + CASE WHEN [ValidatedTmp].[u7] = [UserEnteredTmp].[u7] THEN 0 ELSE 1 END + CASE WHEN [ValidatedTmp].[u9] = [UserEnteredTmp].[u9] THEN 0 ELSE 1 END AS [Status]
                  FROM   @UserEnteredTmp AS UserEnteredTmp INNER JOIN
                         @ValidatedTmp AS ValidatedTmp ON [UserEnteredTmp].[evens] = [ValidatedTmp].[evens]) AS idat
          WHERE  [Status] < 3
         ) AS idd
ORDER BY [vSSN], [ueSSN], [Status];

The ‘SSN matching’ SQL Problem

Follow this question

Related Questions