Jun 27, 2013
kalpesh

MYSQL LIKE match whole word only

Mysql LIKE clause match exact whole word only while searching. Search the words with space in mysql database.

If you want to search for exact word using LIKE clause, you can’t do it just using percentage (%) sign around the keyword as it will search for partial words too. For example, the below query will match all the words which contain “some”:

1
SELECT * FROM tablename WHERE columnname LIKE '%some%';

It will find all the instances where it finds some, as a whole or a part. some, someone, something, handsome, isometric are all MATCHED using above query. Ofcourse there will be many results which are UNRELATED.

But what if you only want some, as an exact word WITH space and WITHOUT any extra heading and trailing characters?

This query will do exactly that:

1
SELECT * FROM tablename WHERE columnname = 'some' OR columnname LIKE '% some' OR columnname LIKE 'some %' OR columnname LIKE '% some %';

In above query we are making sure that all the some instances are retrieved, whether they are present in the START or in the END or in the MIDDLE of the columnname field.

2 Comments

  • This is pretty much the stupidest code I’ve ever seen.

    You could use regexp or the AGAINST keyword.

    SELECT *
    FROM test
    WHERE MATCH (headline,description) AGAINST(‘$string’);

    SELECT *
    FROM db
    WHERE description REGEXP ‘[[::]]’ = 1
    OR headline REGEXP ‘[[::]]’ = 1

  • $str = “exact words”;
    $data_to_find = ‘[[::]]’;
    $sql_master = mysqli_query($db_link,”SELECT * FROM test WHERE my_field REGEXP ‘$data_to_find’”);

Leave a comment

 

Welcome to my Blog

Kalpesh MehtaHelping Magento developers in their day-to-day development problems since 2011. Most of the problems and solutions here are my own experiences while working on different projects. Enjoy the blog and don't forget to throw comments and likes/+1's/tweets on posts you like. Thanks for visiting!

Certifications