Overview
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.
Explanation
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.
Additional Information
Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs.
- MSSQLTips Awards: Author of the Year Contender – 2015-2018, 2022, 2023 | Champion (100+ tips) – 2018
