Sort By Soundex (or similar) `Closeness`

views:

answers:

+1 Q:

Sort By Soundex (or similar) `Closeness`

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.

+1 A:

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.

Paul Sasik 2010-10-20 17:35:40

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 2010-10-20 17:51:54

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 2010-10-20 18:04:26

ansaurus

tags:

views:

answers:

Sort By Soundex (or similar) `Closeness`

related questions