SQL Server DIFFERENCE() Function

 The DIFFERENCE() function in SQL Server is used to compare the similarity of two strings based on their Soundex values and returns a value indicating how similar the strings are. It is particularly useful for phonetic matching, allowing comparison of words that sound alike but are spelled differently.

Syntax

DIFFERENCE(string1, string2)

• string1, string2: The two strings to be compared.

Return Values

The function returns an integer value between 0 and 4, where:

• 0: Strings are completely different.
• 4: Strings are very similar or identical.

The similarity is determined by the Soundex algorithm, which converts the strings to phonetic representations before comparing them.

Example Usage

Basic Example

SELECT DIFFERENCE('Smith', 'Smyth') AS Similarity; -- Output: 4
SELECT DIFFERENCE('Smith', 'Johnson') AS Similarity; -- Output: 0
SELECT DIFFERENCE('Cat', 'Cut') AS Similarity; -- Output: 3

Practical Example: Finding Similar Names

If you have a table of customer names and want to find names similar to a given input:

CREATE TABLE Customers (
    CustomerID INT,
    CustomerName NVARCHAR(50)
);

INSERT INTO Customers (CustomerID, CustomerName)
VALUES
(1, 'Smith'),
(2, 'Smyth'),
(3, 'Johnson'),
(4, 'Smythe');

SELECT CustomerName
FROM Customers
WHERE DIFFERENCE(CustomerName, 'Smyth') >= 3;

Output:

CustomerName
-------------
Smyth
Smith
Smythe

Notes

• The DIFFERENCE() function is case-insensitive.
• It uses the Soundex algorithm internally, so it may not work well for all languages or dialects.
• Useful for approximate matching in tasks like finding typos or phonetic matches in names or addresses.