Recently, I was writing an autocomplete method in a Ruby on Rails application. I wanted to find whole word matches, and words starting with the entered text.
The regex I wanted to use was like this:
word =~ /\W#{query}/
So, 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. Actually, it defines the REGEX
operator, but it doesn’t implement it - 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 (unimplemented) regexp() function. SQLite allows you to add external functions at runtime, so I realized that there must be a way around the limitation, but, initially I thought I had to implement regexp() as a C function.
I found an article about implementing regexp() in Ruby. It needed a bit of tweaking as in the Rails 2.3.x interface for SQLite ActiveRecord::ConnectionAdapters::SQLite3Adapter.initialize()
takes 3 parameters, not 2.
I got REGEXP
working by creating an initializer:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
|
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"]