SQL DIFFERENCE Function

The SQL DIFFERENCE String Function is used to return the difference between the SOUNDEX values of the user-specified character expressions. The syntax of the SQL Server DIFFERENCE Function is:

SELECT DIFFERENCE (Character_Expression, Character_Expression)
FROM [Source]
  • Character_Expression: Please specify the valid character expression.

This difference function returns an integer value ranging from 0 to 4 where 0 means No similarity, 3 means More similarity, and 4 means strong similarity or same.

SQL DIFFERENCE Example 1

The DIFFERENCE function finds the difference between the two character expressions. The below query will show multiple ways to use the difference function.

DECLARE @Str VARCHAR(50)
SET @Str = 'SQL Server'

SELECT DIFFERENCE(@Str, 'SQL Server') AS 'SQL_Difference 1' 

SELECT DIFFERENCE(@Str, 'Server') AS 'SQL_Difference 2' 

SELECT DIFFERENCE(@Str, 'QL Server') AS 'SQL_Difference 3' 

SELECT DIFFERENCE(@Str, 'SQL Server tutorial') AS 'SQL_Difference 4' 

SELECT DIFFERENCE(@Str, 'SQ erv') AS 'SQL_Difference 5' 

SELECT DIFFERENCE(@Str, '123 456789') AS 'SQL_Difference 6'
SQL DIFFERENCE 1

Within this String Function example query, we used this difference function to find the SOUNDEX difference between the variable @Str and ‘SQL Server’.

SELECT DIFFERENCE(@Str, 'SQL Server') AS 'SQL_Difference 1'

DIFFERENCE Function Example 2

The DIFFERENCE function returns the difference between the SOUNDEX values of the two strings. In this example, We are going to return the SOUNDEX code of the character expressions along with the difference. I suggest you refer to the SOUNDEX Function article in SQL Server.

DECLARE @Str VARCHAR(50)
SET @Str = 'SQL Server Difference'

SELECT SOUNDEX(@Str) AS [Code 1],
       SOUNDEX('SQL Server') AS [Code 11],
	   DIFFERENCE(@Str, 'SQL Server') AS 'SQL_Difference 1' 

SELECT SOUNDEX(@Str) AS [Code 2],
       SOUNDEX('QL Server') AS [Code 22],
	   DIFFERENCE(@Str, 'QL Server') AS 'SQL_Difference 2' 

SELECT SOUNDEX(@Str) AS [Code 3],
       SOUNDEX('123 456789') AS [Code 33],
	   DIFFERENCE(@Str, '123 456789') AS 'SQL_Difference 3'
SQL DIFFERENCE 2