SQL fuzzy search and Google-like improvements

Question

Interesting challenge; my client enters some product information in a SQL database. The product is a painting of a famous old Russian composer called Rachmaninoff. So that name is in the description field. Now, only a few of their customers searching for products know exactly how to spell this name, but most of the time it's misspelled. Besides misspelling there are also a lot of international customers who just write this name completely different like, Rachmaninow, Rahmaninov, Рахманінаў.

If i put any of these misspellings or translations in Google it (almost) always knows how to correct it and to redirect me straight to the right page.

Does anyone know what my possibilities are to get some of this magic in my product search? Are there some API's i can use? Some super free text option that i don't know of? Or ...

Answer 1

We solved a similar problem with quite some success: Searching for people (german names) by name given over phone.

E.g.: The very common german last names "Schmidt", "Schmitt", "Schmied", "Schmid", "Schmit" and "Schmiedt" will be all but impossible to hold apart when given by a voice. Combine this with a first name of "Sylvia" or "Silvia" or "Sylvya" and a caller saying "Hi, I'm Sylvia Schmidt, I have forgotten my customer number" has no chance of being quickly found.

Our solution was to put up a list of synophones, e.g. (in pseudo code, for german):

{consonant}+ := {consonant}
ie := i
ii := i
dt* := t
y|j := i
{vocal}v := {vocal}f

etc., you get the drift. Now we stored the synophone-translated strings with the original strings to make search possible. This works really well.

I understand that MySQL has the Soundex() function for English strings. I would expect MSSQL to have something similar.