How to use DIFFERENCE in Sql Server

Intro

The SQL Server DIFFERENCE function returns the a score from 0-4 based on how different two words sound. The DIFFERENCE function uses the SOUNDEX function to compare values of the input strings. A score of 0 means the strings are not similar and a value of 4 means very similar.

The Syntax

The basic syntax of a DIFFERENCE is as follows:

SELECT DIFFERENCE(string1, string2)

Getting Setup

For this, we will be using docker. This is recommended for more than just using SQL Server. To find how to install docker go here: https://docs.docker.com/engine/install/

Now create a file called docker-compose.yml and add the following.

version: "3.9"
services:
  db:
    image: "mcr.microsoft.com/mssql/server"
    ports: 
      - 1433:1433
    environment:
        SA_PASSWORD: "Your_password123"
        ACCEPT_EULA: "Y"

Open a terminal and go to the folder the file is located. Then run the following.

docker-compose up

If you are looking for another good reference, you can check here: https://docs.docker.com/samples/aspnet-mssql-compose/.

Basic Example - Similar Words

For our first example, let's compare the word "sleigh" and "slay"

SELECT DIFFERENCE('sleigh', 'slay') as diff

As we can see these two words are very similar. Let's compare that to their soundex values.

SELECT SOUNDEX('sleigh') as sleigh, SOUNDEX('slay') as slay;  

Example of Difference

Now, let's take a look at an example where the soundex scores are very different and the words sound dissimilar.

SELECT DIFFERENCE('strawberry', 'coffee') as diff

The sore is one as these words are pretty difference in sound. To check, we can also see the soundex scores again.

SELECT SOUNDEX('strawberry') as sleigh, SOUNDEX('coffee') as slay;