204
Part II: Building Databases and Working with Data
Here’s how Soundex works. The fi rst letter of a name is stored as the letter, and the follow-
ing Soundex phonetic sounds are stored according to the following code:1 = B, F, P, V
2 = C, G, J, K, Q, S, X, Z
3 = D, T
4 = L
5 = M, N
6 = R
Double letters with the same Soundex code, A, E, I, O, U, H, W, Y, and some prefi xes, are dis-
regarded. Therefore, “LeBlanc” becomes “L145” via the following method:- The L is stored.
- The e is disregarded.
- The b sound is stored as the Soundex code 1.
- The l is stored as the Soundex code 4.
- The a is disregarded.
- The n is stored as the Soundex code 5.
- The c is ignored.
By boiling them down to a few consonant sounds, Soundex assigns “LeBlanc,” ”LeBlank”
and “LaBlanc” the same code: N145.Following are additional Soundex name examples:■ (^) Brown = B650 (r = 6, n = 5)
■ Jeffers = J162 (ff = 1, r = 6, s = 2)
■ (^) Letterman = L365 (tt = 3, r = 6, m = 5)
■ Nicholson = N242 (c = 2, l = 4, s = 2)
■ (^) Nickols = N242 (c = 2, l = 4, s = 2)
SQL Server includes two Soundex-related functions, SOUNDEX() and DIFFERENCE().
Using the SOUNDEX() Function
The SOUNDEX(string) function calculates the Soundex code for a string as follows:
SELECT SOUNDEX('LeBlanc') AS LeBlanc,
SOUNDEX('LaBlanc) AS LaBlanc,
SOUNDEX(LeBlank) AS LeBlank;
c08.indd 204c08.indd 204 7/30/2012 4:21:16 PM7/30/2012 4:21:16 PM
http://www.it-ebooks.info