0

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}%"})
Yuval Karmi
  • 26,277
  • 39
  • 124
  • 175
  • Text search on databases is slow, expensive and does not scale. You should consider moving this to a full text search solution like Solr os Sphinx if this is a production application. – Maurício Linhares Jul 27 '11 at 02:53
  • 1
    Full Text Search (FTS) is the SQL functionality to provide what you want -- REGEX will work, but never outperform FTS or `LIKE`. – OMG Ponies Jul 27 '11 at 02:56
  • Why does this question have the "ruby" tag? – Andrew Grimm Jul 27 '11 at 03:23
  • @Andrew - because I am implementing this in Ruby on Rails. I'll edit the tags so they are more specific and mention it in my question. – Yuval Karmi Jul 27 '11 at 03:42

1 Answers1

3

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

Jim Garrison
  • 85,615
  • 20
  • 155
  • 190
  • Thanks for the answer. However, this doesn't seem to work with SQLite3 and Ruby on Rails 3.1. I keep getting `SQLite3::SQLException: no such function: REGEXP`. Any ideas how to fix this? Thanks! – Yuval Karmi Jul 27 '11 at 03:33
  • I figured some other people might have this question, so I posted a separate one right here: http://stackoverflow.com/questions/6839204/how-to-turn-on-regexp-in-rails-3-1-and-sqlite-3 – Yuval Karmi Jul 27 '11 at 03:38