DIFFERENCE Function in SQL Server

by

In this SQL Server tutorial, you will learn about the DIFFERENCE function in SQL Server. The DIFFERENCE() function helps in data matching based on the sound of the data.

First, you will be introduced to the DIFFERENCE() function and how it finds the difference between strings. Then, the syntax of the DIFFERENCE() function.

After that, with several examples, you will understand how this function works. Finally, you will know where and how this function can used.

DIFFERENCE Function in SQL Server

The DIFFERENCE function in SQL Server compares the two string values and returns the integer value based on the Soundex system.

In simpler words, the DIFFERENCE function in SQL Server shows the similarities between two strings based on the sound and returns an integer value from 0 to 4; 0 means no similarity, and as the number moves closer to 4, it means substantial similarity.

The Soundex system is a phonetic algorithm that allows indexing the name based on the sound, such as pronounced in English.

The syntax is given below.

DIFFERENCE ( string_value1 , string_value2 )

Where,

  • DIFFERENCE(): This function returns the integer for defining how similar two string values are.
  • string_value1, string_value2: These are the strings to be compared.

The DIFFERENCE() function returns the integer value, which can be any value from 0 to 4.

This integer value defines how the two sound values of the two strings are similar. 0 means there is no similarity if the integer value is four, which means there is an extremely strong similarity between the two strings.

  • Remember, it returns the similarity based on the sound of the two strings, not the number of characters the string contains.

For example, Let’s understand how the DIFFERENCE function in SQL Server works.

Using the query below, let’s test the similarity between the words ‘Walking’ and ‘Vaaking’.

SELECT DIFFERENCE( 'Walking', 'Vaaking');

As you can see from the above output, the similarity between the words ‘Walking’ and ‘Vaaking’ is 2, which means the phonetic sound matches by more than 50%.

Again, change the word ‘Vaaking’ to ‘Vaalking’. Now check the similarity using the query below.

SELECT DIFFERENCE( 'Walking', 'Vaalking') AS Similarity;
DIFFERENCE Function in SQL Server Checking Similarity

The result set contains the integer value 3, which indicates more similarity between the two strings or words; around 80% of characters in both strings may match each other.

and if specify both strings are ‘Walking’, then it returns the value as 4, which means both strings are similar. Look at the output of the query below.

SELECT DIFFERENCE( 'Walking', 'Walking') AS Similarity;
DIFFERENCE Function in SQL Server Strong Similarity

From the above examples, you now understand how the DIFFERENCE function in SQL Server works, but the examples contain similar words. Let’s take one example, but take two words with the same phonetic sound this time.

For example, use the query below to see their similarity for two words, ‘they’ and ‘way’.

SELECT DIFFERENCE( 'they', 'way') AS Similarity;
DIFFERENCE Function in SQL Server Same Phonetic Sound

The DIFFERENCE() function returns the value as 3, meaning 80% similarity between the words ‘they’ and ‘way’. But if you look at the two words, most of the characters in both words don’t match with each other except the character ‘y’.

But it still shows a good amount of similarity because of the phonetic sound, or when you pronounce both words, the sounds are more similar. So here, it is proven that the DIFFERENCE () function shows the similarity of two strings based on the sound.

  • But how will you use this DIFFERENCE function on the table data in SQL Server? For example, you have records of employees, and you need to find the employees whose names sound very similar, and this can be very useful in finding duplicate records.
  • Also, it can be implemented or used in the search functionality to enhance the user experience; for example, while searching for anything, the user may type the wrong spelling of words. In that case, it returns all the relevant results based on the similar sound of the words.

This is how to use the DIFFERENCE function in SQL Server to find how similar two strings are based on the sound of both strings.

Conclusion

In this SQL Server tutorial, you learned how to detect the similarity between two strings using the DIFFERENCE function in SQL Server.

Also, through multiple examples, you learned the DIFFERENCE() function shows the similarity between two strings based on their sound, not the number of similar characters each string contains.

Additionally, you learned the DIFFERENCE() function can be used in the search functionality, data matching, etc.

You may like to read: