SQL Server DIFFERENCE() Function
The DIFFERENCE() function in SQL Server compares two strings based on their phonetic similarity, returning a score indicating how closely they sound. This is extremely useful for fuzzy matching—finding strings that sound alike, even if they aren't spelled identically.
DIFFERENCE(): Definition and Usage
DIFFERENCE() uses the SOUNDEX() function (see the documentation for details on SOUNDEX) to generate a four-character code for each input string representing its phonetic sound. It then compares these codes, returning a similarity score between 0 and 4. A higher score means the words sound more alike. A score of 0 indicates a weak or no similarity, and a score of 4 indicates very strong similarity or identical SOUNDEX codes.
Syntax
Syntax
DIFFERENCE(expression, expression)
Parameter Values
| Parameter | Description |
|---|---|
expression, expression |
The two strings to compare. Both are required. Can be literals, variables, or column names. |
Examples
Comparing Similar-Sounding Words
This compares "Juice" and "Jucy". They sound alike, and we expect a high similarity score.
Syntax
SELECT DIFFERENCE('Juice', 'Jucy');
Output
4
Comparing Dissimilar Words
Comparing "Juice" and "Banana"—these sound quite different, so we expect a low score.
Syntax
SELECT DIFFERENCE('Juice', 'Banana');
Output
1