Using LIKE in sql query

Question

In a query in which I am looking for results based on the title of articles, I use the LIKE part as followed:

WHERE title LIKE %searchquery%

In my database one title is like this:

Economy in America

My problem:

With my current query, this title does NOT get listed, when the user enters the following searchquery:

america economy

When the user enters only one of these terms, everything works fine and the title gets listed.

How come?

How do I need to adjust my query so that my sql query will also work when the user enters more than one term?

If the more terms are in the same column, you could try replacing all whitespaces with % signs eg.: WHERE title LIKE %america%economy% — kayess
– kayess, Commented Jun 18, 2015 at 9:28
MySQL with PHP. Using "AND" works. the title, however does not get displayed, when the user has a typo, for example when he enters "amrica economy" or "america ecnomy"... how can I also consider typos for the title to appear? — Max
– Max, Commented Jun 18, 2015 at 9:38
@Max thats a completely different story, you should start learning about terms like Levenshtein distance, Soundex, generating and comparing key similarity. — kayess
– kayess, Commented Jun 18, 2015 at 10:07

Tim Biegeleisen · Accepted Answer · 2015-06-18 09:28:53Z

1

The MySQL LIKE operator is limited in that it doesn't offer full regex support of the sort for which you are really looking. That being said, one option you could try would be to split your search query terms and then join multiple LIKE conditions using AND. Consider this example:

WHERE title LIKE %queryterm1% AND LIKE %queryterm2%

If queryterm1 be 'america' and queryterm2 be 'economy', then this would match the title 'Economy in America'.

answered Jun 18, 2015 at 9:28

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Max Over a year ago

this works, thank you. the title, however does not get displayed, when the user has a typo, for example when he enters "amrica economy" or "america ecnomy"... how can I also consider typos for the title to appear?

Tim Biegeleisen Over a year ago

There is no easy way to do this with MySQL AFAIK. Have a look here for an article which will get you started thinking about this challenging problem.

mrks · Accepted Answer · 2015-06-18 09:29:43Z

0

You will need to split the query by whitespaces and then add multiple LIKE statements like this:

WHERE title LIKE '%america%' AND title LIKE '%economy%'

answered Jun 18, 2015 at 9:29

mrks

8,4532 gold badges37 silver badges67 bronze badges

Comments

user586399 · Accepted Answer · 2015-06-18 09:30:19Z

0

As @Tim says, you must write some routine in your application to divide the string terms and inject them in the select where clause. Or you may need to write some stored procedure for that.

answered Jun 18, 2015 at 9:30

user586399

Collectives™ on Stack Overflow

Using LIKE in sql query

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related