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
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
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
14 People are following this question.
