1

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?

Cœur
  • 37,241
  • 25
  • 195
  • 267
crmepham
  • 4,676
  • 19
  • 80
  • 155

2 Answers2

1

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

jszobody
  • 28,495
  • 6
  • 61
  • 72
  • Thanks, could you briefly explain what that REGEXP statement does for those that are Manual phobic? – crmepham Apr 13 '13 at 23:42
  • 1
    I updated my answer. Those two markers match the beginning and end of words. REGEXP stands for Regular Expression. – jszobody Apr 13 '13 at 23:44
0

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.

TheEwook
  • 11,037
  • 6
  • 36
  • 55
  • 1
    That won't work for titles where it starts or ends with the word "in" – jszobody Apr 13 '13 at 23:33
  • True! so you can do this LIKE '% in %' or LIKE '%in %' or LIKE '% in%'. I admit it's not the best solution. It's just a way to achieve what he wants. There are probably a much better way. – TheEwook Apr 13 '13 at 23:35