Home > SQL Server, T-SQL > SQL SERVER- DIFFERENCE() soundex function @SQLSERVER

SQL SERVER- DIFFERENCE() soundex function @SQLSERVER


Difference() string function gives the difference between the SOUNDEX code of two character expressions.

Function Syntax

DIFFERENCE ( character_expression , character_expression )

Difference function returns integer value range from 0 to 4. We can use this value to find the similarity between both character_expression.

Return value meaning

  • 0 (Zero) represents no similarity between two strings.
  • 4 represents strong similarity between two strings.
  • Between  1 and 3, represents very close but not strong similarity.

We can use this number to make our own decision in our application. Learn more about soundex code and SOUNDEX function

Points to remember

  1. Difference() in SQL Server is collation sensitive.
  2. Difference() generates soundex code for each character expression and returns similarity value between them.

Experiment 1:

select DIFFERENCE(‘apple’, ‘orange’)

It has no similarity. So, the output is 0 (Zero)

Experiment 2:

select DIFFERENCE(‘apple’, ‘pineapple’)

It has some similarity. So, the output is 2

Experiment 3:

select DIFFERENCE(‘animal’, ‘anemal’)

it has strong similarity. So, the output is 4

You can use this function in your application to find the string based on similarity value between the stored data and the search string given by the user in your application.

For instance:

Execute the following statement in SQL Server 2012.

use AdventureWorks2012
select * from person.Person where DIFFERENCE(firstname , ‘jo’) between 3 and 4

Thanks for reading

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: