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