question

SQLcuriosity avatar image
SQLcuriosity asked

How does DIFFERENCE function actually work?

Using SQL Server 2012 and adventureworks2012 database, run the following query: **select distinct lastname, soundex(lastname) as soundex, difference(lastname,'smith') as difference from person.person where soundex(lastname) = soundex('smith')** All returned soundex values are the same, yet the difference column for surname 'Schmidt' is returned as 3. I understand Microsoft's explanation of the difference function to be a method of comparing the soundex values of two String values using a scoring system, and the scoring is based on the number of differing characters in the two soundex values (maximum score of 4 being given for 4 matching characters). Is my understanding correct, and if so is there any way of explaining this inconsistency? Thanks in advance
sql server 2012characters
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
Your understanding is correct. I can replicate the results on SQL2012 (and SQL2014). Interestingly if you run this on SQL2008R2, you get a different SOUNDEX value for 'Schmidt' select soundex('smith'), soundex('Schmidt'), soundex('Smith-Bates'), soundex('Sneath'), difference ('smith','Schmidt') I would certainly say there is an inconsistency - you should raise this on [Connect][1] [1]: http://connect.microsoft.com
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SQLcuriosity avatar image
SQLcuriosity answered
Sorry for delay responding. Thanks for letting me know that I was on the right track! I have raised this issue on connect. Many thanks
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

post the link here and we can upvote it
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.