Searching Mysql for simillar string

Question

People have different ideas of how to search for the same term.

For example Tri-Valley, Trivalley, Tri Valley (and possibly even incorrect spellings)

Currently that search is done like this

SELECT * FROM `table` WHERE `SchoolDistrict` LIKE '%tri valley%';

Is there an easy way to say 'space dash or no space' without writing out three like statements?

It seems like it could easily be done:

SELECT * FROM `table` WHERE `SchoolDistrict` LIKE '%tri%valley%';

But this only works if the initial input is 'tri-valley' or 'tri valley' If the initial input is 'trivalley' I have no idea where to place the % (theoretically that is, actually, I do, as we are only looking at about a dozen different school districts, but I'm looking to solve the larger problem)

Answer 1

You could consider using SOUNDEX, or SOUNDS LIKE if you have a lot of incorrect spellings. If you've got a lot of rows (or even if you don't), it might be wise to store the output of the SOUNDEX in an additional column.

I'd also recommend -- in the interests of accuracy -- introducing a separate table with an authoritative list of school districts, and run a query to find those which aren't in that list.

Answer 2

MySQL has a function called Sounds like.

link text

Answer 3

The second statement you posted should do the trick:

SELECT * FROM 'table' WHERE 'SchoolDistrict' LIKE '%tri%valley%';

What you should do before you pass the search term into the select statement is to replace all characters and spaces with the % sign. For example,

SearchTerm = SearchTerm.Replace(" ","%");

Answer 4

An alternative here is to recast the problem from search to select, if possible. Instead of letting your users enter free-form text to choose a school district, if you have a set of school districts generate a dropdown (or set of cascading dropdowns if the list is large, say by county, then by school district) and allow the user to select the appropriate one. Use this both for "searching" and for data entry to eliminate non-canonical entries. Obviously this only works when you can enumerate all of the entries.

Alternatively you could allow the user to choose a starts with or contains type search and simply generate the appropriate SQL ('tri%' or '%tri%') based on the selected search type. If the user understands that the search type is starts with or contains, they will likely adjust their search string until it yields the results they need.