Regular Expressions in SQLite with Ruby

Posted by Joe Yates Sun, 31 Jan 2010 11:30:00 GMT

Recently, I was writing an autocomplete method in a Ruby on Rails application.

If the user entered 'and', I wanted to retrieve 'Andes' and 'Bill and Ben', but not 'candle'.

In development I was using an SQLite database, and SQLite does not yet implement a regular expression operator (it defines it, but if you use it you get an error).

I tried writing the method with just the 'LIKE' operator, but it was getting very long-winded: you have to jump through hoops to approximate the regex '\W' operator.

The REGEXP operator is just syntactic sugar for the regexp() function (https://www.sqlite.org/lang_expr.html"https://stephen-veit.blogspot.com/2009/03/implementing-regexp-in-sqlite3.html">an article about implementing regexp() in Ruby, but the code in the article doesn't work with the interface Rails 2.3.x interface for SQLite - ActiveRecord::ConnectionAdapters::SQLite3Adapter.initialize() takes 3 parameters, not 2.

I got REGEXP working by creating an initializer 'config/initializers/sqlite3_regexp.rb':

require 'active_record/connection_adapters/sqlite3_adapter'

class ActiveRecord::ConnectionAdapters::SQLite3Adapter
  def initialize(db, logger, config)
    super
    db.create_function('regexp', 2) do |func, pattern, expression|
       regexp = Regexp.new(pattern.to_s, Regexp::IGNORECASE)
       if expression.to_s.match(regexp)
         func.result = 1
       else
         func.result = 0
       end
     end
  end
end

The proof of the pudding:

./script/console
>> Noun.find(:all, :conditions => ['name LIKE ?', '%and%']).collect(&:name)
=> ["Candle", "Bill and Ben", "Andes"]
>> Noun.find(:all, :conditions => ['name REGEXP ?', '\Wand']).collect(&:name)
=> ["Bill and Ben", "Andes"]

Leave a comment

Comments