How to turn on REGEXP in SQLite3 and Rails 3.1?

user_118175 0
Viewed 10351

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.

ruby-on-rails sqlite ruby sql regex
6 Answers

From source of sqlite3_ar_regexp project, I extract this:

    db = ActiveRecord::Base.connection.raw_connection
    db.create_function('regexp', 2) do |func, pattern, expression|
      func.result = expression.to_s.match(
          Regexp.new(pattern.to_s, Regexp::IGNORECASE)) ? 1 : 0
    end

Improved upon a previous answer with ActiveRecord::Base.connection.raw_connection so that db name isn't needed

user_78037320
Related