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 had a similar question, and found a Gem named wherex that is well documented and worked out of the box.

Your expression from above

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

would there be

Word.where(:name => Regexp.new("[[:<:]]#{word}[[:>:]]"))

Works like a charm for me :-)

Answer 2

From the fine manual:

The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If a application-defined SQL function named "regexp" is added at run-time, that function will be called in order to implement the REGEXP operator.

So the grammar supports REGEXP but the default SQLite library does not provide an implementation for it. You'll have to hook up your own implementation through some C wrangling if you want or need such a thing.

Presumably the rationale is that the SQLite people want to keep SQLite as small and tight as possible but including a whole regular expression library would add weight that most people don't want. Also, they would have to choose a regular expression library and include it with the SQLite source or they'd have to put up with the vagaries of everyone's regular expression support in libc. I'm not one of the SQLite developers so this is pure speculation.

I'm guessing that you'll probably have to make do with LIKE and GLOB. Using LIKE will provide a more portable solution.

Answer 3

From source of sqlite3_ar_regexp project, I extract this:

db = SQLite3::Database.open( database_name )
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

Answer 4

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

Answer 5

You may be intested in the sqlite3-pcre package, which implements REGEXP for SQLite.

See this comment on a similar issue.