Flexible SELECT command for similar strings

[hmc]

I have a table containing song titles. I want a user who may not be a good typist to be able to search the table effectively.

Suppose I have "American Pie" in my table. How can I construct a SELECT statement that would find this record if the user entered "American Pie" or "American Pi" or "American Pye" or just "Pie"? I don't see how REGEXP or LIKE or SOUNDEX would cover all these possibilities.

Sites like Amazon.com handle this very effectively. Surely there must be a way. Anybody got some experience with this?

Thanks,

- hmc

 

[rycamor]

Yes, this is not a place for regex, unless your search has to be quite complex and specific.

The 'LIKE' operator is a very central piece of the SQL language, used along with % an _. This query would find American Pie, or any other record where that column *contains* your search term.

SELECT * FROM yourtable WHERE song LIKE '%$search_term%'

If you just want a "begins with" kind of search, you could do

SELECT * FROM yourtable WHERE song LIKE '$search_term%';

so then the user must enter the beginning, such as "American" or "Amer", etc... Obviously, there is a lot more complexity you can add just by adding successive arguments, such as

SELECT * FROM yourtable WHERE song LIKE '$search_term%' AND WHERE somethingElse LIKE "$search_term2_';

... and so on. The _ operator matches only one character in addition to the specified string. There is a lot more you can do with MySQL, though, if you start reading the documentation at

http://www.mysql.com/doc/S/E/SELECT.html,

and

http://www.mysql.com/doc/F/u/Functions.html