Comparing SOUNDEX and DIFFERENCE
The SOUNDEX function converts a character string to a four-digit code for use in a comparison. Vowels are ignored in the comparison. Nonalphabetic characters are used to terminate the comparison. This function always returns some value.
This example displays the results of the SOUNDEX function for the similar character strings of "Smith" and "Smythe". When character strings are similar, both strings have the same SOUNDEX codes.
SELECT SOUNDEX ('smith'), SOUNDEX ('smythe')
Here is the result set:
----- -----
S530 S530
(1 row(s) affected)
The DIFFERENCE function compares the SOUNDEX values of two strings and evaluates the similarity between them, returning a value from 0 through 4, where 4 is the best match. This example returns a DIFFERENCE of 4 for the first SELECT because "Smithers" and "Smothers" differ by only one character.
SELECT DIFFERENCE('smithers', 'smothers')
Here is the result set:
------------
4
(1 row(s) affected)
The following example returns a DIFFERENCE of 3, indicating that the two character strings have a similar sound even though they differ in several characters.
SELECT DIFFERENCE('Jeff', 'Geoffe')