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 end the comparison. This function always returns some value.
The following 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');
GO
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. The following example returns a DIFFERENCE of 4 for the first SELECT because smithers and smothers differ by only one character.
SELECT DIFFERENCE('smithers', 'smothers');
GO
Here is the result set.
------------
4
(1 row(s) affected)
The following example returns a DIFFERENCE of 3. This indicates that the two character strings have a similar sound even though they differ in several characters.
SELECT DIFFERENCE('Jeff', 'Geoffe');
GO
Here is the result set.
-----------
3
(1 row(s) affected)
See Also
Other Resources
SOUNDEX (Transact-SQL)
DIFFERENCE (Transact-SQL)
String Functions (Transact-SQL)