T-SQL coding

A Quick word about SOUNDEX

For those of you that use the SOUNDEX function: Please note that this function changed after SQL 2008 R2. Microsoft must have realized that they did not follow the exact algorithm for this function, so they changed it starting in SQL 2012. That means this function MAY return a different value than it used to.  No big deal, unless, like my current project, the SOUNDEX value is saved in a column (so we can index and search more quickly).

SELECT SOUNDEX(‘SCOTT’) now returns a different value than it used to since we upgraded to SQL 2014, but the old value was stored in our table, so a search came up empty. Easy fix, we just had to update the data in that column to the new SOUNDEX result and all was good. The big change was that adjoining consonants are treated differently than they were before, changing the result. The old value was S230, and the new value is now S300. You can refer to the National Archives’ web site for more information on the algorithm using this link:

https://www.archives.gov/research/census/soundex.html

I found that Rule 2 changed in SQL Server starting in SQL 2012, and I bet that rules 3 and 4 were also altered.

Advertisements

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