SOUNDEX and DIFFERENCE Functions

SQL Server String Functions

SQL Server string functions are scalar functions that perform an operation on a string input value and return a string or numeric value.

SOUNDEX and DIFFERENCE Functions

The other two string functions available in SQL Server are SOUNDEX and DIFFERENCE, which happen to be rather similar and very seldom used. SOUNDEX provides a four character representation of the string (SOUNDEX code) and is supposed to help you determine whether two strings sound alike. For example, the following query retrieves SOUNDEX values for a few employees:

SELECT  SOUNDEX(LastName) AS soundex_code,  LastName  FROM DimEmployee

Results:

soundex_code string_value
S650 Sharma
S100 Shoop
S150 Spoon
S520 Song
S520 Singh
S550 Simon
S530 Smith

The DIFFERENCE function provides a degree of similarity (or lack thereof) between the two character expressions. If the SOUNDEX values are the same for the two strings passed to the DIFFERENCE function then the degree of similarity is the highest - 4. Otherwise, the DIFFERENCE function will return 3, 2, 1 or 0. The DIFFERENCE function could be used when you wish to find all customers with a name that sounds similar to a known value, as in the following example:

SELECT LastName FROM DimEmployee WHERE DIFFERENCE (LastName, 'que') > 2

Results:

LastName
Mu
Liu
Wu
Liu
Poe
Li
Loh
Nay