# Thread: Matching SSN's - what digit is different

1. Registered User
Join Date
Jul 2003
Location
San Antonio, TX
Posts
3,662

## Unanswered: Matching SSN's - what digit is different

Someone probably already answered this question, but I guess my search phrases are to blame for sitting empty-handed thus far.

I could write something like this as a CLR function, but the restrictions are:
- No CLR;
- No bulk-insert;
- No xp_cmdshell;
- And I am sure there will be more "No...'s".

I could also do some crazy recursive looping across digits. But I hope that there is some function like checksum(...) that could tell me what digit is different between 2 strings comprised of digits (SSN's in my case).

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
This is horrifically simple-minded, but:
Code:
```CREATE TABLE #rdjabarov (
a		VARCHAR(9)
,  b		VARCHAR(9)
)
INSERT INTO #rdjabarov VALUES ('123456789', '123456789')
INSERT INTO #rdjabarov VALUES ('123456789', '023456789')
INSERT INTO #rdjabarov VALUES ('123456789', '103456789')
INSERT INTO #rdjabarov VALUES ('123456789', '120456789')
INSERT INTO #rdjabarov VALUES ('123456789', '123056789')
INSERT INTO #rdjabarov VALUES ('123456789', '123406789')
INSERT INTO #rdjabarov VALUES ('123456789', '123450789')
INSERT INTO #rdjabarov VALUES ('123456789', '123456089')
INSERT INTO #rdjabarov VALUES ('123456789', '123456709')
INSERT INTO #rdjabarov VALUES ('123456789', '123456780')

SELECT z.a, z.b, MIN(CASE WHEN Left(z.a, y.i) <> Left(z.b, y.i) THEN y.i END)
FROM #rdjabarov AS z
CROSS JOIN (SELECT 1 AS i UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS y
GROUP BY z.a, z.b```
-PatP

3. Registered User
Join Date
Sep 2001
Location
Chicago, Illinois, USA
Posts
603
Clever. I like it.

4. Registered User
Join Date
Jul 2003
Location
San Antonio, TX
Posts
3,662
Yep, I knew it was very near, like right behind my back...Thanks!

Here's what I ended up writing (algorythm developed by Pat Phelan):
Code:
```createtable #numbers(nbr intnotnull)
declare @i int= 1, @PatientSSN varchar(12), @RealSSN char(9)
select @RealSSN ='123456789', @PatientSSN ='123056498'
while @i < 10 begin
insert #numbers(nbr)select @i
set @i += 1
end
select*from (
select RealSSN=@RealSSN, PatientSSN=@PatientSSN, DigitNumber =
case
whensubstring(@PatientSSN, nbr, 1)!=substring(@RealSSN, nbr, 1)then nbr
elsecast(nullasint)
end
from #numbers
) x where DigitNumber is not null
go
```
Last edited by rdjabarov; 07-02-10 at 16:50. Reason: formatting

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•