10

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.

Yuval Karmi
  • 26,277
  • 39
  • 124
  • 175

6 Answers6

15

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

Aaron Lasseigne
  • 205
  • 2
  • 6
  • And for that, you get brownie points (and your answer marked as the correct one). Thank you thank you thank you! – Yuval Karmi Nov 18 '11 at 09:39
8

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.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
3

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

See this comment on a similar issue.

Community
  • 1
  • 1
tricasse
  • 1,299
  • 13
  • 18
2

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 :-)

mliebelt
  • 15,345
  • 7
  • 55
  • 92
0

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
raubarede
  • 423
  • 3
  • 6
0

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

King X KoK
  • 1