Microsoft® SQL Server® 2012 Bible

206

Part II: Building Databases and Working with Data

matching rows, because the stored Soundex code is extremely fast. You can use a similar

query to the following, assuming the new column is named SoundexCode:

To accomplish this you could add a new column to the Person table in the AdventureWorks

data. The column could be a persisted calculated column, so it is automatically calculated

for every insert and kept updated with every update.

USE AdventureWorks

GO

SELECT LastName, FirstName, SoundexCode

FROM Person.Person

WHERE SoundexCode = 'A536';

Result:

LastName FirstName SoundexCode

------------ -------------- -----------

Andersen Alejandro A536

Andersen Alicia A536

...

Using the DIFFERENCE() Soundex Function

The second SQL Server Soundex function, DIFFERENCE(), returns the Soundex difference

between two strings in the form of a ranking from 1 to 4, with 4 representing a perfect

Soundex match:

USE AdventureWorks2008

GO

SELECT

LastName,

DIFFERENCE('LeBlanc', LastName) NameSearch

FROM Person.Person

ORDER BY DIFFERENCE('LeBlanc', LastName) DESC

Result:

LastName NameSearch

------------- -----------

Kobylinski 3

Shabalin 3

Slattengren 2

Slaven 2

...

The advantage of the DIFFERENCE() function is that it broadens the search beyond

the fi rst letters. The problem with the function is that it wants to calculate the

c08.indd 206c08.indd 206 7/30/2012 4:21:17 PM7/30/2012 4:21:17 PM

http://www.it-ebooks.info