Sunday, March 15, 2009

Implementing REGEXP in Sqlite3

I am implementing a rails application that required searching using a regular expression. I am using MySQL for the production database, but SQLite3 for the test database. Now SQLite3 supports the REGEXP SQL operator, however it is left to the user to implement the function. I found a post by Rolando Abarca where he attempted to implement regexp in Ruby. I adapted that and also created a rails initializer (config/initializers/sqlite3_regexp.rb):

require 'active_record/connection_adapters/sqlite3_adapter'

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

This allowed me to implement a named_scope to search for a set of comma (or semicolon) separated names:

class Subject < ActiveRecord::Base
named_scope :with_names, lambda {|names|
names = names.strip.split(/ *[,;] */).join('|')
if !names.blank?
{:conditions => ["name regexp ?", names]}

Which could then be called like:

subjects = Subject.with_names("math, english")

See this post on my site Veit Consulting.

1 comment:

Wolf said...

Regular expression is really wonderful to parsing HTML or matching pattern. I use this a lot when i code. Actually when I learn any new langauge, first of all I first try whether it supports regex or not. I feel ezee when I found that.

Here is about ruby regex. This was posted by me when I first learn ruby regex. So it will be helpfull for New coders.