Sort By Soundex (or similar) `Closeness`

Question

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.

Butterfingers

Answer 1

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.

Dhu

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. – Butterfingers

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) – Dhu

@Rinzler: Yeah, this post is almost two years old. Links disappear. In any case, I found another example of a MySql implementation and relinked. – Dhu

thanks my friend , can you find me a soundex implemetation in zend framework – Industrialist

@Rinzler: Check out this PHP manual page for working with Levenshtein: php.net/manual/en/function.levenshtein.php I'm not familiar with Zend but you should be able to leverage the algorithm. – Dhu