SQL Server DIFFERENCE Function


By:

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.

Value Description Example SOUNDEX STRING 1 SOUNDEX STRING 2
0 Weak similarity or no similarity SELECT DIFFERENCE('Green','aaa'); G650 A000
1 Poor similarity SELECT DIFFERENCE('GreenS','g'); G652 G000
2 Medium similarity SELECT DIFFERENCE('GreenS','Graa'); G652 G600
3 Strong similarity SELECT DIFFERENCE('GreenS','Green'); G652 G650
4 The strongest similarity SELECT DIFFERENCE('Green','Green'); G650 G650

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


Last Update: 11/3/2021




Comments For This Article





download














get free sql tips
agree to terms