User Tips: Finding Similar Sounding Names using SOUNDEX
This tip comes from Lee Jae
| Introduction |
|---|
The situation: you've got a database containing information about each employee in your company. You
decide to provide a search interface, allowing fellow employees to search for information on their
coworkers by their last or first names. The only problem? People are searching for terms like Mike,
and no results are being returned because, perhaps, Mike's name is stored as Michael in the database.
Urg. Ideally, you'd like to return results that sound like Mike, but how can you do this? Actually,
it's quite simple with SQL Server's built in SOUNDEX option! Read on to learn more!
|
I've encountered a situation where client wanted a utility that finds a matches of similar sounding
names. For example, when typing "BILL", it needs to also find "WILL" or "WILLIAM". This is difficult
because a computer has no innate concept of hearing - a computer is good at black and white comparisons
(x = y), but usually pretty bad at fuzzy ones (is x "like" y?)
Initially, in a quest to solve this problem, I attempted to use the LIKE SQL
statement; however, such an approach yielded unwanted results. For example, when using LIKE %ANA%,
words like tatiANA are returned as well.
The solution, I soon discovered, was SQL Server's SOUNDEX statement. What this statement
does is to find similar "sounding" names in the database by ignoring all vowels and converting the
strings into four-digit codes where comparison can be made.
In order to show the usefulness of SOUNDEX, let's look at a simple example that searches
for records in the following data table:
At this point, you can create a simple search engine interface that, say, allows the user to enter a
First or Last name to search on, and returns results that sound like the first or last name entered.
For example, if we wanted to search for entries whose first name sounded like "John" we could run the
following query:
SELECT FirstName + ' ' + LastName
FROM tblEmpoyees
WHERE SOUNDEX('John') = SOUNDEX(FirstName)
|
Essentially, the above query states: "Give me all rows whose FirstName column sounds like
"John". In creating a search interface, you'd have the user-entered query inserted where we have the
hard-coded value of "John". The above query would return two rows: John Badagliacca
and Jean-Pierre Blaise.
Another option available is to use the DIFFERENCE keyword. The DIFFERENCE
keyword returns a value from 1 to 4, indicating how closely one word
matches another phonetically (1 being loosely similar and 4 being very similar). Using this approach
you could specify a threshold of, say, 3, or perhaps even let the user define his or
her own threshold. To learn more about DIFFERENCE and SOUNDEX read:
SOUNDEX String Comparison.
Happy Programming!
