Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662

    Angry 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 OPENSOURCE/Linked server;
    - 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).
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Clever. I like it.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  4. #4
    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 17:50. Reason: formatting
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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