SQL Server – Difference and SOUNDEX function with example

Difference function returns integer value as output, value ranges between 0 to 4. Lower number indicates less chance of matching or no match and highest number 4 indicates exact match. Difference function is based on SOUNDEX function in sql server which returns alphanumeric value.
Lets go through simple examples of DIFFERENCE function and SOUNDEX function.

1. Example

SELECT DIFFERENCE(‘Sandy’,’S$$’) as [Result of Difference Function]
select SOUNDEX(‘S$$’)as [SOUNDEX Value for’S$$’]
select SOUNDEX(‘Sandy’) as [SOUNDEX Value for ‘Sandy’]

Output

Result of Difference Function
—————————–
2
SOUNDEX value for’S$$’
———————-
S000
SOUNDEX Value for ‘Sandy’
————————-
S530
Here you can see S and 0 is common in both results of SOUNDEX funciton that is partially mismatching,

2.Example

Here we are using two non matching value one is Football and another is Cricket. Lets see the output
select SOUNDEX(‘Football’)as [SOUNDEX Value for Football]
select SOUNDEX(‘Cricket’) as [SOUNDEX Value for Cricket]
SELECT DIFFERENCE(‘Football’,’Cricket’) as [Result of Difference Function]

Output

SOUNDEX Value for Football
————————–
F314
SOUNDEX Value for Cricket
————————-
C623
Result of Difference Function
—————————–
1
You can see number 3 is matching in both output of SOUNDEX function so difference output is 1.

3. Example of No matching value of SOUNDEX and difference function.

select SOUNDEX(‘@#$’)as [SOUNDEX Value]
select SOUNDEX(‘Cricket’) as [SOUNDEX Value for Cricket]
SELECT DIFFERENCE(‘@#$’,’Cricket’) as [Result of Difference Function]

Output

SOUNDEX Value
————-
0000
SOUNDEX Value for Cricket
————————-
C623
Result of Difference Function
—————————–
0

4. Example of Exact match value of difference function and SOUNDEX function

select SOUNDEX(‘Cricket’)as [SOUNDEX Value for Cricket]
select SOUNDEX(‘Cricket’) as [SOUNDEX Value for Cricket]
SELECT DIFFERENCE(‘Cricket’,’Cricket’) as [Result of Difference Function]

Output

SOUNDEX Value
————-
C623
SOUNDEX Value for Cricket
————————-
C623
Result of Difference Function
—————————–
4

Leave a Reply

  

  

  


+ 3 = 11