T-SQL Tutorial T-SQL Advanced Database Management T-SQL References

T-SQL DIFFERENCE() Function



The T-SQL (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

str1 Required. Specify the first string whose soundex string is to be compared.
str2 Required. Specify the second string whose soundex string is to be compared.

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:

DataWord1Word2
Data1HereHeir
Data3SmithSmithing
Data4SmytheSmythy
Data5ToTwo
Data6TooTwo
Data7TwoThree

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:

DataWord1Word2DIFFERENCE_Value
Data1HereHeir4
Data3SmithSmithing3
Data4SmytheSmythy4
Data5ToTwo4
Data6TooTwo4
Data7TwoThree3

❮ T-SQL Functions