INF: DIFFERENCE() and SOUNDEX() Functions in Transact-SQL

ID: Q100365


The information in this article applies to:


SUMMARY

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.


MORE INFORMATION

The algorithm for determining the level of similarity between two character strings is outlined below:


given:  level = difference(a1, a2)
        then
                sx1 = soundex(a1)
                sx2 = soundex(a2)
                where soundex returns:
                <alpha><numeric><numeric><numeric> 

The algorithm first generates the soundex of a1 (sx1) and a2 (sx2) then:
Otherwise:
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
^  ^    ^^
|--+----||
(1)|-----|
   (2) 

Additional query words: Windows NT


Keywords          : kbother SSrvTran_SQL SSrvServer 
Version           : 4.2 | 4.2
Platform          : OS/2 WINDOWS 
Issue type        : 

Last Reviewed: March 16, 1999