Understanding The SQL DIFFERENCE() Function With Example

Summary: In this tutorial, you will learn SQL DIFFERENCE() Function and how to use them in an SQL server to compare two SOUNDEX() values of two strings.

Definition and Usage

The SQL Server DIFFERENCE() function compare between two SOUNDEX values, and returns an integer value. These integer indicates the match for the two SOUNDEX values, from 0(zero) to 4.

0 integer value indicates weak or no similarity between the SOUNDEX values. and 4 integer value indicates strong similarity or identically SOUNDEX values.

.
Note: The DIFFERENCE function in SQL Server is used to return the difference between the SOUNDEX values of the user-specified character expressions.

DIFFERENCE() Function Syntax

The syntax of the DIFFERENCE Function is:

DIFFERENCE(expression, expression)

In this syntax:

  • expression, expression: It is required value, here, Two expressions to be compared. Can be a variable, constant, or column
.
Return Value This difference() function returns an integer value from 0 to 4 where 0 means No similarity, 3 means More similarity, and 4 means strong similarity or same. expressions.

Understanding the SQL DIFFERENCE() function

The given below is a string, Therefore, the SOUNDEX() function converts it to a 4-character code
based on how the string sounds when it is spoken. For illustration, both Due and Dew words sound
the same, so they should have the same SOUNDEX() values:

SELECT 
    SOUNDEX('Due') Due, 
    SOUNDEX('Dew') Dew;

Due   Dew
----- -----
D000  D000
(1 row affected)

The DIFFERENCE() function in SQL Server returns an integer value measuring the difference between the SOUNDEX() values of 2 (two) strings.

SQL Server DIFFERENCE() function

Let’s take some illustration of using the DIFFERENCE() function to see how this function works.

Using SQL DIFFERENCE() function with similar SOUNDEX() values
This illustration uses the DIFFERENCE() function to compare the SOUNDEX() values of two string Due and Dew:

SELECT 
    SOUNDEX('Due') soundex_due,
    SOUNDEX('Dew') soundex_dew,
    DIFFERENCE('Due', 'Dew') similarity;

soundex_due soundex_dew similarity
----------- ----------- -----------
D000        D000        4
(1 row affected)

Let’s see the another illustration that compares the difference in SOUNDEX() values of Hair and Hare:

SELECT 
    SOUNDEX('Hair') soundex_hair,
    SOUNDEX('Hare') soundex_hare,
    DIFFERENCE('Hair', 'Hare') similarity;

soundex_hair soundex_hare similarity
----------- ----------- -----------
H600        H600        4
(1 row affected)

Using SQL Server DIFFERENCE() function for different SOUNDEX() values

The following example illustrates that how the DIFFERENCE() function works when comparing two strings that have a weak or not similar sounds:

SELECT 
    SOUNDEX('Broom') soundex_broom,
    SOUNDEX('Spoon') soundex_spoon,
    DIFFERENCE('broom', 'spoon') similarity;

soundex_broom soundex_spoon similarity
----------- ----------- -----------
B650        S150        2
(1 row affected)

Conclusion

In this tutorial, you have learned DIFFERENCE() Function and how to use them in an SQL server to compare two SOUNDEX() values of two strings.

About Author

admin