CODEWITHSUNDEEP
phpmysqlsearchsql-like
crmepham
crmepham

Reputation: 4740

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?

Upvotes: 1

Views: 98

Answers (2)

jszobody
jszobody

Reputation: 28911

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

Upvotes: 1

TheEwook
TheEwook

Reputation: 11117

Do like this:

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

If it's a word you are sure there are space before and after the word in question.

Upvotes: 0

Related Questions