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 ("">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)
    db.create_function('regexp', 2) do |func, pattern, expression|
       regexp =, Regexp::IGNORECASE)
       if expression.to_s.match(regexp)
         func.result = 1
         func.result = 0

The proof of the pudding:

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