SQL Server DIFFERENCE() Function

Summary: in this tutorial, you will learn how to use the SQL Server DIFFERENCE() function to compare two strings and determine their phonetic similarity.

Introduction to the SQL Server DIFFERENCE() function

The DIFFERENCE() function compares two strings and determines their phonetic similarity.

Here’s the syntax of the DIFFERENCE() function:

DIFFERENCE (string1, string2)Code language: SQL (Structured Query Language) (sql)

In this syntax, the string1 and string2 are two strings you want to compare.

The DIFFERENCE() function returns an integer value ranging from 0 to 4:

• 4: indicates that the two strings are very similar phonetically.
• 3 indicates that the two strings are similar.
• 2 indicates that the two strings are somewhat similar.
• 1 indicates that the two strings are not very similar.
• 0 indicates that the two strings are completely dissimilar.

The DIFFERENCE() function is useful when you want to perform fuzzy string matching or search based on sound similarity rather than exact matches.

The DIFFERENCE() function returns NULL if either of the input strings is NULL.

SQL Server DIFFERENCE() function examples

Let’s explore some examples of using the DIFFERENCE() function.

1) Basic DIFFERENCE() function example

The following example uses the DIFFERENCE() function to compare two strings “hello” and “hallo” if their phonetics are similar:

SELECT DIFFERENCE('hello', 'hallo') AS SimilarityScore;Code language: SQL (Structured Query Language) (sql)

Output:

SimilarityScore
---------------
4Code language: SQL (Structured Query Language) (sql)

In this example, the strings "hello" and "hallo" have a similarity score of 4, indicating that they are very similar phonetically.

2) Using the DIFFERENCE() function in the WHERE clause

We’ll use the sales.customers table from the sample database:

The following example uses the DIFFERENCE() function to find the customers whose last names have a phonetic similarity score with the word "Tod":

SELECT 
  first_name, 
  last_name 
FROM 
  sales.customers 
WHERE 
  DIFFERENCE(last_name, 'Tod') = 4;Code language: SQL (Structured Query Language) (sql)

Output:

3) Using the DIFFERENCE() function with NULL

The DIFFERENCE() function returns NULL if either of the input strings is NULL. To handle NULL appropriately, you can use the ISNULL() function. For example:

SELECT DIFFERENCE(ISNULL('Hi', ''), ISNULL(NULL, '')) AS SimilarityScore;Code language: SQL (Structured Query Language) (sql)

Output:

SimilarityScore
---------------
0Code language: SQL (Structured Query Language) (sql)

Summary

• Use the SQL Server DIFFERENCE() function to compare two strings and determine their phonetic similarity.