SQL DIFFERENCE() is one of the String Functions, compares two SOUNDEX values, and returns the integer. An integer value indicates the match for the two SOUNDEX values, 0 to 4. The 0 shows weak or no similarity between the SOUNDEX values. The 4 indicates strong similarity or identically SOUNDEX values.
SQL DIFFERENCE()
SQL DIFFERENCE() is a built-in function used to return the difference between the Soundex values. The Soundex value is four characters long. If two strings sound the same, their Soundex values will be the same. However, if two strings sound entirely different, none of the character’s Soundex values will be the same.
For example, peace and piece sound the same to have the same Soundex values.
SELECT SOUNDEX ('piece') AS Piece, SOUNDEX ('Peace') AS Peace;
See the output.
| Piece | Peace |
| P200 | P200 |
See the following syntax.
SELECT DIFFERENCE (input_string1, input_string2);
Parameters
- DIFFERENCE: Used for comparing two Soundex values.
- Input_string1, input_string_2: Strings whose Soundex values are to be considered.
Examples
- STRONG SIMILARITY
Query
SELECT SOUNDEX('PIECE') AS PIECE, SOUNDEX('PEACE') AS PEACE, DIFFERENCE ('PIECE', 'PEACE')
AS SIMILARITY;
Output
As the Soundex values of both the strings are the same and there is a strong similarity between them, 4 is returned.
Query
SELECT SOUNDEX (‘TWO’) AS TWO, SOUNDEX(‘TOO’) AS TOO, DIFFERENCE (‘TWO’, ‘TOO’) AS SIMILARITY;
Output
| TWO | TOO | SIMILARITY |
| T000 | T000 | 4 |
Less Similarity
See the following query.
SELECT SOUNDEX (‘COFFEE’) AS COFEE, SOUNDEX(‘LAPTOP’) AS LAPTOP, DIFFERENCE (‘COFFEE’, ‘LAPTOP’) AS SIMILARITY;
Output
| COFFEE | LAPTOP | SIMILARITY |
| C100 | L131 | 1 |
In the above query, both the strings, sounds were different, so there was a minimal similarity between the two strings. Therefore, 1 was returned as output because 1 character of Soundex values had a match.
No Similarity
Query
SELECT SOUNDEX('Tree') AS Tree, SOUNDEX('Captivated') AS Captivated, DIFFERENCE ('Tree', 'Captivated')
AS SIMILARITY;
Output
| Tree | Captivated | SIMILARITY |
| T600 | C131 | 0 |
In the above output, there is no match between any characters. So, the value returned was 0.
That’s it for this tutorial.
