SQLite3::SQLException: no such function: REGEXP rails rspec

Question

In my project, I have different time slices. These time slices have names such as "00-04", "04-08", "08-12", "12-16", "16-20" and "20-24" and more. I wanted to get all the time slices objects with aforementioned names. So, I programmed following:

time_slice_names = ["00-04", "04-08", "08-12", "12-16", "16-20", "20-24"]
time_slices = TimeSlice.where('name REGEXP ?', time_slice_names.join("|"))

time_slices gives me correct objects in my development mode. However, when I run the test, I have the errors as:

 ActiveRecord::StatementInvalid:
 SQLite3::SQLException: no such function: REGEXP: SELECT "time_slices".* FROM "time_slices"  WHERE (name REGEXP '00-04|04-08|08-12|12-16|16-20|20-24')

My database.yml is as follows:

  development:
    adapter: mysql2
    database: development 
    encoding: utf8
    pool: 5
    timeout: 5000

  test:
    adapter: sqlite3
    database: db/test.sqlite3
    pool: 5
    timeout: 5000

I am aware that my test environment uses sqlite3 whereas my development environment uses mysql2. Is there anyway where I can perform the above mentioned query on both mysql2 and sqlite3?

I find it better to use the same DB adapter for dev/test/prod. However, check [this](http://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query) out (ignore the accepted answer). — ndnenkov, Oct 13 '16 at 14:49
@sadiksha Me too don't have much knowledge on the SQLite part. So if it works with MySQL and not SQLite, n the error clearly says that SQLite doesn't have implemented that regexp function. So better use the same Db for all envs as ndn suggested. — millisami, Oct 14 '16 at 03:22
Thanks you and @ndn, for this case I used the query for handling arrays. I will definitely consider your suggestion. — Sadiksha Gautam, Oct 17 '16 at 14:47
@SadikshaGautam it would be best if you post an answer with your current solution and accept it for future users to find. — ndnenkov, Oct 17 '16 at 14:53

score 1 · Accepted Answer · edited May 23 '17 at 12:00

So according to different answers in How to turn on REGEXP in SQLite3 and Rails 3.1?, regexp() is not by default defined in Sqlite3. You have to install a native extension which adds this functionality to Sqlite3.

However, your query can be done without using Regular expressions.

time_slice_names = ["00-04", "04-08", "08-12", "12-16", "16-20", "20-24"]
time_slices = TimeSlice.where(name: time_slice_names)

The internal database adapter (mysql or sqlite3) should understand this and convert it to supporting where field in (x, y, z) query.

More information available ActiveRecord where field = ? array of possible values

SQLite3::SQLException: no such function: REGEXP rails rspec

1 Answers1