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.

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

Solution 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.

Solution 3

Try using regular expressions:

SELECT 
    *
FROM
    `products`
WHERE
    product_name regexp '(^|[[:space:]])BLA([[:space:]]|$)';

Solution 4

SELECT  *
FROM    products
WHERE   product_name = 'BLA'

will select exact BLA

SELECT  *
FROM    products
WHERE   product_name LIKE 'BLA%'

will select BLADDER and BLACKBERRY but not REBLAND

To select BLA as the first word of the string, use:

SELECT  *
FROM    products
WHERE   product_name RLIKE '^Bla[[:>::]]'
        AND product_name LIKE 'Bla%'

The second condition may improve your query performance if you have an index on product_name.

Solution 5

Remove LIKE keyword and use = for exact match

EDIT

do not forgot to escape user input using mysql_real_escape_string otherwise your query will fail if some one enter quotes inside the input box.

$search=mysql_real_escape_string($search);
mysql_query("SELECT * FROM products WHERE product_name='".$search."'");

View more solutions

Comments

[Mike]

Well, I didn't express myself clearly, the product_name column consists of many more words, than just the product_name itself. For example, there can be something like "Bla - 50g", "Bla - 10g", "Bla - 5g" and I want to select all "Bla's", but not "Blabla's".

[Mike]

Well, I didn't express myself clearly, the product_name column consists of many more words, than just the product_name itself. For example, there can be something like "Bla - 50g", "Bla - 10g", "Bla - 5g" and I want to select all "Bla's", but not "Blabla's".

[Mike]

Well, there can be absolutly anything, so that wouldn't work. =/

[anothershrubery]

What do you mean there could be absolutely anything?! If you are talking about the hyphen (-), just remove it and leave the space. That will only match words beginning with "Bla " and will therefore not select "Blabla" but will select "Bla -" or "Bla Bla". I only added the hyphen (-) as the examples you gave all had a hyphen.

[Ata]

I do not think the number one works well, think if 'foo ' value !

[Sr. Libre]

The drawback of using RLIKE is: "The REGEXP and RLIKE operators work in byte-wise fashion, so they are not multibyte safe and may produce unexpected results with multibyte character sets. In addition, these operators compare characters by their byte values and accented characters may not compare as equal even if a given collation treats them as equal."

[Kevin UI]

I had the same question and was overthinking it. This works great. Simple, readable, and good performance. No regex necessary.

[Mārtiņš Briedis]

What about BLA? Or BLA! or BLA, bla bla, or "bla–"...

[HadiNiazi]

great dear, it helps me a lot thank you so much