sql - How to turn on REGEXP in SQLite3 and Rails 3.1?

Question

I have the following statement in Rails 3 using an SQLite3 database:

word = 'Hello'
word_entry = Word.where("name REGEXP :word", {:word => "[[:<:]]#{word}[[:>:]]"})

However, when running this under SQLite3, I keep getting:

SQLite3::SQLException: no such function: REGEXP

I read in the SQLite3 documentation that it does indeed support the REGEXP function. In my gemfile, I have the line

gem 'sqlite3'

And my database config file looks like this:

development:
  adapter: sqlite3
  database: db/development.sqlite3
  pool: 5
  timeout: 5000

Any ideas what's going on?

RESOLUTION: I ended up finding this solution. Unfortunately, it doesn't work for Rails 3. So to use regular expressions I ended up switching to MYSQL instead of SQLite3.

Answer 1

I ran into the same issue. I took the code used in the resolution, ported it to work with Rails 3+ and made a gem for easier use. I hope this helps.

https://github.com/sei-mi/sqlite3_ar_regexp