CODEWITHSUNDEEP
mysqlsqlselectsql-like
Mike
Mike

Reputation: 6934

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

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.

Upvotes: 39

Views: 131134

Answers (12)

Goldie
Goldie

Reputation: 1630

You can just cover all the possible options.

SELECT 
    * 
FROM 
    `products` 
WHERE
    `product_name` like 'BLA' -- Column cointains just that word
    OR `product_name` like 'BLA %' -- The word appears at the beginning
    OR `product_name` like '% BLA' -- The word appears at the end
    OR `product_name` like '% BLA %'; -- The word appears in the middle

Upvotes: 1

Hadayat Niazi
Hadayat Niazi

Reputation: 2470

In Laravel Eloquent you can do this like below.

Category::where('name', 'RLIKE ', "[[:<:]]"$words"[[:>:]]");

In raw query, you can search it like this.

SELECT * FROM categories WHERE name RLIKE "[[:<:]]categoryNameHere[[:>:]]";

Upvotes: 0

Riead Ahmed Bhiyan
Riead Ahmed Bhiyan

Reputation: 307

If you want to search exact word matching from MySql using LIKE then you use: 

SELECT * FROM tableName WHERE columnName LIKE 'your_query' ;

Upvotes: 0

Faisal
Faisal

Reputation: 4767

Try using regular expressions:

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

Upvotes: 10

Rahul Dhiman
Rahul Dhiman

Reputation: 29

you can use select query like this ,i also use in cakePHP and it's helpful.

Select * from `users` where username COLLATE latin1_general_cs LIKE '%$email%'

Upvotes: -1

PulpDood
PulpDood

Reputation: 432

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.

Upvotes: 14

Pankaj katiyar
Pankaj katiyar

Reputation: 464

try to use regular expression in query 

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

Upvotes: 0

James C
James C

Reputation: 14149

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

Upvotes: 55

Quassnoi
Quassnoi

Reputation: 425251

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.

Upvotes: 8

anothershrubery
anothershrubery

Reputation: 20993

Use equals (=)?

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

If you are looking to match EXACT words don't use LIKE.

EDIT: That clears things up a bit then. Just add a space after the search term. Or even add the hyphen (-) if that is always in the search term.

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

Upvotes: 0

Shakti Singh
Shakti Singh

Reputation: 86336

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."'");

Upvotes: 2

wimvds
wimvds

Reputation: 12850

Then don't use LIKE, but search for equality.

ie.

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

BTW I hope you sanitize/escape $search before using it in a query.

Upvotes: 1

Related Questions