Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Flexible SELECT command for similar strings

Flexible SELECT command for similar strings

Flexible SELECT command for similar strings

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?


 - hmc

RE: Flexible SELECT command for similar strings

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close