Search code examples
phpmysqlsearchsql-like

When selecting rows based on a where clause how can I match full words?

I am trying to create search functionality for a website. In MySQL I would like to match words exactly, for example if I have a couple of posts with these titles:

A day in time

and

A coming day

and I have the following search string:

SELECT title
        FROM posts
        WHERE title LIKE '%in%'
        ORDER BY Date
        DESC

It will return both rows because the LIKE term %in%shows up in both in in the first row and coming in the second row.

But I only want it to match whole words, so that only the first row would be returned.

Is this possible?

Solution

  • You're looking for a MySQL word boundary REGEXP:

    SELECT title
  FROM posts
  WHERE title REGEXP '[[:<:]]in[[:>:]]'

    From the MySQL docs:

    These markers stand for word boundaries. They match the beginning and end of words, respectively. A word is a sequence of word characters that is not preceded by or followed by word characters.

    http://dev.mysql.com/doc/refman/5.1/en/regexp.html