T-SQL Difference Function Parameter Order

Posted on 2004-12-01
Last Modified: 2008-01-09
I have been doing some work De-Duplicating contacts using the Difference function in SQL server 2000 and have come across on odd result. You receive a different return value depending on the order in which you supply the parameters e.g

Difference('A','Andy') = 3
Difference('Andy','A') = 2

Does anyone know why this is?
Question by:TomNorton
    1 Comment
    LVL 13

    Accepted Solution

    This might help understand the function and the order it is working at.

    The DIFFERENCE() and SOUNDEX() functions in Transact-SQL allow searches on character strings that "sound similar." SOUNDEX() converts each string into a 4-digit code. DIFFERENCE() can then be used to evaluate the level of similarity between the soundexes for two strings as returned by SOUNDEX(). For example, these functions could be used in a case where you wanted to look at all rows that sound like "Erickson," so it should find those with "Erickson," "Erikson," "Ericson," "Ericksen," "Ericsen," and so on.
    The algorithm for determining the level of similarity between two character strings is outlined below: given:  level = difference(a1, a2)
                    sx1 = soundex(a1)
                    sx2 = soundex(a2)
                    where soundex returns:

    The algorithm first generates the soundex of a1 (sx1) and a2 (sx2) then:
    • if all characters in sx2 match all characters in sx1 (position respective) then level = 4.  
    • if <alpha> of sx1 is same as <alpha> of sx2, then starting level is 1; otherwise starting level is 0. Now, looping through sx1 and sx2, the level starts to "grow" by comparing one character in sx2 to all characters in sx1. If there is a match, then we increment the level and the next scan on sx1 will start from the location of the match. If no match exists, we compare the next character in sx2 to the entire 4 character list of sx1. Thus, our pointer in sx2 moves along one character at a time for every iteration and the pointer to sx1 always starts at the beginning of sx1 except in the case of a match and the location of the match becomes the starting point in sx1 for the next iteration. Whenever there is a match the level is increased. A 4 means all of the soundex characters are the same in both strings (location and value). A value of 0 means that there was no value match.  
    The following example illustrates this process:
    sx1     sx2
    A120    A102 <-- soundex values
    ....    ....
    0123    0123 <-- character position

    iteration 1: level starts at 1 because sx2[0] == sx1[0]

    iteration 2:
    start compare with sx2[1] and sx1[1] because of match
    sx2[1] == sx1[1] so level is now 2

    iteration 3:
    start compare with sx2[2] and sx1[2] because of match
    sx2[2] == sx1[3] so level is now 3

    We have now run out of characters in sx1 as a match was on the last
    character of sx1 so difference returns a value of 3.

    Some more examples (remember we are always comparing sx2 to sx1):
    sx1     sx2     difference
    A123    A123    4
    A123    B123    3
    A321    A123    2
    ^  ^    ^^


    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now