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

Question

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 1

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[[:>:]]"; 

Answer 2

Found this question on Google, so I figure that some people may still stumble upon this so here's my pretty inelegant attempt:

SELECT * FROM products WHERE product_name LIKE 'BLA %' #First word proceeded by more words OR WHERE product_name LIKE '% BLA' #Last word preceded by other words OR WHERE product_name LIKE '% BLA %' #Word in between other words OR WHERE product_name = 'BLA'; #Just the word itself 

Not sure about the efficiency or if this covers all cases, so feel free to downvote if this is really inefficient or too inelegant.