CODEWITHSUNDEEP
mysqlruby-on-railsrubyregexsqlite
Yuval Karmi
Yuval Karmi

Reputation: 26713

How to search an independent word in a mySQL search?

I am trying to find the word fox in the sentence The foxy brown fox jumped over the lazy dog. Currently, I search using the following SQL:

SELECT * 
  FROM sentences 
 WHERE sentence LIKE '%fox%'

This finds the sentence due to the presence of foxy, not just fox. How do I find fox independently?

I am implementing this search in Ruby on Rails, and so the syntax for the above SQL would be translated to the following:

query = 'fox'
result = Sentence.where("sentence LIKE :search_term", {:search_term => "%#{query}%"})

Upvotes: 0

Views: 203

Answers (1)

Jim Garrison
Jim Garrison

Reputation: 86774

You need to use the REGEXP facility along with "word boundary" expressions. [[:<:]] and [[:>:]]. For example 

SELECT * FROM sentences WHERE sentence REGEXP '[[:<:]]fox[[:>:]]'

This will handle cases where 'fox' is preceded or followed by a comma, start/end of string, or other non-word character. A word character is defined as [_A-Za-z0-9]. Here's a link to the doc: http://dev.mysql.com/doc/refman/5.1/en/regexp.html

Upvotes: 3

Related Questions