The Language of SQL- P22:Research has shown that, being pressed for time, most readers tend to skip the introduction of any book they happen to read and then proceed immediately to the first real chapter. With that fact firmly in mind, we will only cover relatively unimportant material in the introduction, such as an explanation of what you will and will not learn by reading this book. | Matching by Sound 91 The result is FirstName LastName Cary Grant Matching by Sound Let s turn from matching letters and characters to matching sounds. SQL provides two functions that give you some interesting ways to compare the sounds of words or phrases. The two functions are SOUNDEX and DIFFERENCE. Let s first look at an example that utilizes the SOUNDEX function SELECT SOUNDEX Smith AS Sound of Smith SOUNDEX Smythe AS Sound of Smythe The result is Sound of Smith Sound of Smythe S530 S530 The SOUNDEX function always returns a four-character response which is a sort of code for the sound of the phrase. The first character is always the first letter of the phrase. In this case the first character is S because both Smith and Smythe begin with an S. The remaining three characters are calculated from an analysis of the sound of the rest of the phrase. Internally the function first removes all vowels and the letter Y. So the function takes the MITH from SMITH and converts it to MTH. Likewise it takes the MYTHE from SMYTHE and converts it to MTH. It then assigns a number to represent the sound of the phrase. In this example that number turns out to be 530. Since SOUNDEX returns a value of S530 for both Smith and Smythe you can conclude that they probably have very similar sounds. Microsoft SQL Server provides one additional function called DIFFERENCE which works in conjunction with the SOUNDEX function. 92 Chapter 9 Inexact Matches DATABASE DIFFERENCES MySQL and Oracle The difference function isn t available in MySQL or Oracle. Here s an example using the same words SELECT DIFFERENCE Smith Smythe AS The Difference The result is The Difference 4 The DIFFERENCE function always requires two arguments. Internally the function first retrieves the SOUNDEX values for each of the arguments and then compares those values. If it returns a value of 4 as in the previous example that means that all four characters in the SOUNDEX value are identical. A value of 0 means that none of
