8

Is there any way to have MySQL order results by how close they 'sound' to a search term?

I'm trying to order fields that contain user input of city names. Variations and misspellings exist, and I'd like to show the 'closest' matches at the top.

I know soundex may not be the best algorithm for this, but if it (or another method) could be reasonable successful - it may be worth having the sorting done by the database.

Dori
  • 915
  • 1
  • 12
  • 20
Tim Lytle
  • 17,549
  • 10
  • 60
  • 91

1 Answers1

4

Soundex is no good for this sort of thing because different words can give you the same Soundex results and will therefore sort arbitrarily. A better solution for this is the Levenshein Edit Distance algorithm and you may be able to implement it as a function in your database: Link to Levensheint impl. as MySql stored function!!!

You can also check out this SO link. It contains a Sql server (T-SQL-specific) implementation of the algorithm but it should be possible to port. The mechanics of the algorithm are fairly simple needing only a 2D array and looping over string.

Community
  • 1
  • 1
Paul Sasik
  • 79,492
  • 20
  • 149
  • 189
  • I was looking at Levenshtein if I had to do the sorting in the code. Seems like it weighing implementing the *correct* algorithm in the database, or using the same algorithm that's already available on the code side of things. – Tim Lytle Oct 20 '10 at 17:51
  • If you implement it as a MySql function (link in answer) then you should be able to do it in your SQL. Something like: SELECT CityName, Leven(CityName, compString) FROM City ORDER BY Leven(CityName, compString) – Paul Sasik Oct 20 '10 at 18:04
  • @Rinzler: Yeah, this post is almost two years old. Links disappear. In any case, I found another example of a MySql implementation and relinked. – Paul Sasik Sep 12 '12 at 13:52
  • thanks my friend , can you find me a soundex implemetation in zend framework – Rinzler Sep 13 '12 at 05:39
  • @Rinzler: Check out this PHP manual page for working with Levenshtein: http://php.net/manual/en/function.levenshtein.php I'm not familiar with Zend but you should be able to leverage the algorithm. – Paul Sasik Sep 13 '12 at 13:38