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.
| 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.
