SQL Server DIFFERENCE Function

By: Daniel Calbimonte

The DIFFERENCE function is used to provide an integer value showing the number of common characters between the SOUNDEX output of 2 strings or expressions. The values returned by the function have a range between 0 to 4 where 0 has a weak or similarity and 4 means that the strings are strongly similar or identical.

Syntax

DIFFERENCE(stringToCompare1, stringToCompare2)

Parameters

• stringToCompare1 - This is the first string that we want to compare.
• stringToCompare2 - This is the second string that we want to compare.

Simple DIFFERENCE Example

Below is a simple example of using DIFFERENCE. We will use the difference function to measure the difference between 2 simple similar strings:

SELECT DIFFERENCE('GreenS','Green')

This returns the value of 3, which means the strings are very similar.

Understanding DIFFERENCE Function

The following table shows the possible returned values and the meaning. Basically, DIFFERENCE compares the number of common characters returned by the SOUNDEX function.

Let’s check the examples using the SOUNDEX function:

SELECT SOUNDEX('gorgeous'), SOUNDEX('g')

The SOUNDEX functions return the two following values: G622 and G000

Basically, DIFFERENCE counts the number of common characters returned by the SOUNDEX function. In this case, "G622" and "G000" has the "G" in common. So, if we run the following:

SELECT DIFFERENCE('gorgeous','g');  

The value returned is 1.

Simple DIFFERENCE Example Explained

So, if we issue the first query again:

SELECT DIFFERENCE('GreenS','Green');  

This returns the value of 3. Which means that the strings are similar.

If we check the SOUNDEX values of "GreenS" and "Green":

SELECT SOUNDEX('Green'), SOUNDEX('GreenS')

We get the following output: G652 G650. As you can see, 3 characters are the same, so this is why DIFFERENCE returned a value of 3.

Related Articles

• SQL SOUNDEX