Tag Archives: regexp

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)
    super
    db.create_function('regexp', 2, :numeric) 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

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]}
    else
      {}
    end
  }
end

Which could then be called like:

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