How to turn on REGEXP in SQLite3 and Rails 3.1?

I ran into the same issue. I took the code used in the resolution, ported it to work with Rails 3+ and made a gem for easier use. I hope this helps.

https://github.com/sei-mi/sqlite3_ar_regexp

From the fine manual:

The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If a application-defined SQL function named "regexp" is added at run-time, that function will be called in order to implement the REGEXP operator.

So the grammar supports REGEXP but the default SQLite library does not provide an implementation for it. You'll have to hook up your own implementation through some C wrangling if you want or need such a thing.

Presumably the rationale is that the SQLite people want to keep SQLite as small and tight as possible but including a whole regular expression library would add weight that most people don't want. Also, they would have to choose a regular expression library and include it with the SQLite source or they'd have to put up with the vagaries of everyone's regular expression support in libc. I'm not one of the SQLite developers so this is pure speculation.

I'm guessing that you'll probably have to make do with LIKE and GLOB. Using LIKE will provide a more portable solution.