Make MySQL LIKE Return Full Word matches Only

Question

I dont want rows to be returned where the LIKE is matching a partial word. I am splitting strings on whitespace and then generating a query that will find a match, but its returning matches for partial words. Here is an example

SELECT ID from VideoGames WHERE Title Like "%GI%" AND Title Like "%JOE%"

Returns a match where title = "Yu-Gi-Oh! Power of Chaos: Joey the Passion".

I know only matching full words wont completely resolve the issue, but it will hugely increase accuracy. What can i do to return what i want rather than this.

Add ` ` blanks between `%` and the actual word, like `'% word %'`? — Hartmut Holzgraefe, Dec 08 '14 at 21:56
what if the word is at the end of the string. 'GI JOE' wont math % JOE % will it? — Dan Hastings, Dec 08 '14 at 21:57
Use a regex with word boundary: http://dev.mysql.com/doc/refman/5.1/en/regexp.html — juergen d, Dec 08 '14 at 21:58

score 1 · Accepted Answer · answered Dec 08 '14 at 22:04

You can use RLIKE, the regular expression version of LIKE to get more flexibility with your matching.

SELECT ID from VideoGames 
    WHERE Title RLIKE "[[:<:]]GI[[:>:]]" AND Title RLIKE "[[:<:]]JOE[[:>:]]"

The [[:<:]] and [[:>:]] markers are word boundaries marking the start and and of a word respectively. You could build a single regex rather than the AND but I have made this match your original question.

Make MySQL LIKE Return Full Word matches Only

1 Answers1

Linked