DIFFERENCE (Transact-SQL)

Applies to: 👁 Image
SQL Server 👁 Image
Azure SQL Database 👁 Image
Azure SQL Managed Instance 👁 Image
Azure Synapse Analytics 👁 Image
Analytics Platform System (PDW) 👁 Image
SQL analytics endpoint in Microsoft Fabric 👁 Image
Warehouse in Microsoft Fabric 👁 Image
SQL database in Microsoft Fabric

This function returns an integer value measuring the difference between the SOUNDEX() values of two different character expressions.

👁 Image
Transact-SQL syntax conventions

Syntax

DIFFERENCE ( character_expression , character_expression )

Arguments

character_expression

An alphanumeric expression of character data. character_expression can be a constant, variable, or column.

Return types

int

Remarks

DIFFERENCE compares two different SOUNDEX values, and returns an integer value. This value measures the degree that the SOUNDEX values match, on a scale of 0 to 4. A value of 0 indicates weak or no similarity between the SOUNDEX values; 4 indicates strongly similar, or even identically matching, SOUNDEX values.

DIFFERENCE and SOUNDEX have collation sensitivity.

Examples

The first part of this example compares the SOUNDEX values of two very similar strings. For a Latin1_General collation, DIFFERENCE returns a value of 4. The second part of the example compares the SOUNDEX values for two very different strings, and for a Latin1_General collation, DIFFERENCE returns a value of 0.

A. Return a DIFFERENCE value of 4, the least possible difference

SELECT SOUNDEX('Green'),
 SOUNDEX('Greene'),
 DIFFERENCE('Green', 'Greene');
GO

Here's the result set.

----- ----- -----------
G650 G650 4

B. Return a DIFFERENCE value of 0, the highest possible difference

SELECT SOUNDEX('Blotchet-Halls'),
 SOUNDEX('Greene'),
 DIFFERENCE('Blotchet-Halls', 'Greene');
GO

Here's the result set.

----- ----- -----------
B432 G650 0

Related content

• SOUNDEX (Transact-SQL)