SQL Server DIFFERENCE() Function

The SQL Server (Transact-SQL) DIFFERENCE() function returns an integer value measuring the difference between the SOUNDEX() values of two different character expressions.

This value measures the degree that the SOUNDEX values match, on a scale of 0 to 4. A value of 0 indicates weak or no similarity between the SOUNDEX values; 4 indicates strongly similar, or even identically matching, SOUNDEX values.

The SOUNDEX() function converts an alphanumeric string to a four-character code that is based on how the string sounds when spoken in English. The first character of the code is the first character of str, converted to upper case. The second through fourth characters of the code are numbers that represent the letters in the str. The letters A, E, I, O, U, H, W, and Y are ignored unless they are the first letter of the str. Zeroes are added at the end if necessary to produce a four-character code.

SOUNDEX codes from different strings can be compared to see how similar the strings sound when spoken. The DIFFERENCE() function performs a SOUNDEX on two strings, and returns an integer that represents how similar the SOUNDEX codes are for those strings.

Syntax

DIFFERENCE(str1, str2)

Parameters

Return Value

Returns an integer value measuring the difference between the SOUNDEX() values of two different character expressions.

Example 1:

The example below shows the usage of DIFFERENCE() function.

SELECT DIFFERENCE('Hello', 'Hallo');
Result: 4

SELECT DIFFERENCE('Principal', 'Principle');
Result: 4

SELECT DIFFERENCE('To', 'Too');
Result: 4

SELECT DIFFERENCE('Smith', 'Smithing');
Result: 3

Example 2:

Consider a database table called Sample with the following records:

The following query can be used to get the difference of SOUNDEX codes when the records of Word1 column and Word2 column are compared.

SELECT *, DIFFERENCE(Word1, Word2) AS DIFFERENCE_Value FROM Sample;

This will produce a result similar to: