Adding Google ‘sounds like’ to your sites search

comments

It seems we live in an age where a lot of things are taken for granted on the web. Alternative spellings of things being offered to us when we search is a common example of this – simply look at Google and Bing’s “Did you mean X?” and you’ll know exactly what I'm talking about. At first though this can be seem quite daunting for the everyday web developer, but luckily Microsoft SQL server is here to save the day!

Microsoft doesn’t “SUCK TEH BIG TARM LOLZ”

your_operating_system_sucks_trollcat That’s right kids, once again the view that a lot of those PHP, Ruby and Mac player haters have couldn’t be further from the truth, the evil Microsoft has some oober cool love to share and it is in the form of the Soundex function, and if i don’t say so myself i think its pretty darn sweet.

The way the Soundex function works is by converting a string to a code that represents its phonetics so that you can find similar words that may sound different. It does this by by ignoring all vowels.

It’s Doug not DUG!

This idea reminds me a lot of a Birthday card i got on my 5th birthday from one of my school friends. He spelt my name “Dug” on my birthday card and it pissed me off at the time to no end even though i was so young.

A very simple way to test this out on the Soundex comman is to pull up a new query in SQL management studio and enter the following two lines

PRINT soundex('doug')
PRINT soundex('dug')

Straight away you’ll probably get a little bit excited as you’ll see that they both come back with the result “D200”. This means they both have the same phonetic signatures – cool huh?

Adding a search query to the mix

While the above function is cool when working with simple comparisons we want more than that. We want to take a query someone has entered and try and find a similar phrase from a table we have so that we can offer the user this ability.

SELECT TOP 1 'Did you mean "' + FirstName + '"?' AS SoundsLike
FROM tblNames
WHERE SOUNDEX('Doug') = SOUNDEX(FirstName)

The above example simply returns a string that says “Did you mean X?” where X is the FirstName field of my table tblNames with the search criteria looking for names that sound like Doug.

Change this up for your own solution and your done!