MySQL - How to search for exact word match using LIKE?

mysql select sql-like
Mike picture Mike · Apr 21, 2011 · Viewed 96.9k times · Source

I'm using this query to select data:

mysql_query("SELECT * FROM products WHERE product_name LIKE '%".$search."%'");

The only problem is, that it sometimes selects more, than I would like.

For example, I would like to select product "BLA", but my query select product "BLABLA" as well. To be clear, if i wanted to select "Product 1", I don't want the query to select "Product 11".

Does anybody know how to manage that?

Thanks.

Answer

James C picture James C · Apr 21, 2011

Do you just want to search on word boundaries? If so a crude version might be:

SELECT * FROM products WHERE product_name LIKE "% foo %";

Or you could be a bit cleverer and look for word boundaries with the following REGEXP

SELECT * FROM products WHERE product_name RLIKE "[[:<:]]foo[[:>:]]";

Related questions

MySQL SELECT LIKE or REGEXP to match multiple words in one record

The field table.name contains 'Stylus Photo 2100' and with the following query SELECT `name` FROM `table` WHERE `name` LIKE '%Stylus 2100%' I get no results. Of course i would if i searched SELECT `name` FROM `table` WHERE `name` …

Read More
How to ignore characters on a MYSQL SELECT LIKE %...%

I have a table with a phone column, where data may have spaces, dots,dashes or + signs between the numbers. I need to do a search with LIKE wildcards that ignore all those characters, for example: a record may have …

Read More
MySQL - UPDATE query based on SELECT Query

I need to check (from the same table) if there is an association between two events based on date-time. One set of data will contain the ending date-time of certain events and the other set of data will contain the …

Read More