0

I am trying to search for a shop name in one of MySQL table, the table has a field called fullname. As of now I am using the SOUNDS LIKE method of MySQL however here's an example that failed:

Say I have the string Banana's Shop. Then using SOUNDS LIKE with query of 'nana' or 'bananas' won't give me the result. Here's my current query:

SELECT `fullName` FROM `shop` WHERE `fullName` SOUNDS LIKE 'nana';

is there a better way to do simple search like this in MySQL that is smarter so that typo's would also still match?

adit
  • 32,574
  • 72
  • 229
  • 373

1 Answers1

0

The ancient and slightly honorable SOUNDEX algorithm used by SOUNDS LIKE doesn't handle suffix sounds. That is, nana doesn't, and can't, match banana. banani will match banana, however.

Two utterances don't necessarily sound alike unless they have the same number of syllables. It's good for matching stuff like surnames: Smith, Schmitt, and Schmidt all have the same SOUNDEX value.

Calling SOUNDEX 'smart text search' is an exaggeration. http://en.wikipedia.org/wiki/Soundex

You might consider MySQL FULLTEXT search, which you can look up. This does a certain amount of phrase matching. That is, if you had "banana shop" and "banana slug" in your column, the word "banana" would have a shot at matching both those values.

Be careful with FULLTEXT. It works counterintuitively when you have less than about a couple of hundred rows in the table you're searching.

But that's not a typo-friendly word matcher. What you're asking isn't really easy.

You could consider the Levenshtein algorithm (which you can look up). But it's a hairball to get working properly.

O. Jones
  • 103,626
  • 17
  • 118
  • 172