Can I find all values LIKE ["_bc","a_c","ab_"]

Question

Just usuing SQLlite3 can I find the values that are LIKE what I am looking for and pass in an array?

e.g. I'd like something of the sort

Url.where("token LIKE ?" ["_bc","a_b","ab_"])

Don't forget you can [use regular expressions in SQLlite](http://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query). — tadman, Apr 15 '16 at 18:04
Looks like you have to install a special package. Not a big deal but I am trying to solve it with just out the box SQLlite. For no good reason... — slindsey3000, Apr 15 '16 at 20:09

score 1 · Answer 1 · answered Apr 15 '16 at 18:01

You could try somehting like this (untested for SQLite):

class Url < ActiveRecord::Base
  scope :with_tokens, lambda{|*tokens|
    query = tokens.length.times.map{"token LIKE ?"}.join(" OR ")
    where(query, *tokens)
  }
end

Url.with_tokens("_bc", "a_b", "ab_")

score 1 · Accepted Answer · answered Apr 15 '16 at 18:11

No. You'll have to repeat the LIKE clauses for each of the condition, something like this:

like_conditions = %w(_bc a_b ab_)
Url.where((["token LIKE ?"] * like_conditions).join(" OR "), *like_conditions)
# => SELECT `urls`.* FROM `urls` WHERE (token like '_bc' OR token like 'a_b' OR token like 'ab_')

The first part of the where clause constructs an array of token LIKE ? strings with the same length as the number of like conditions and joins them using OR (change this to ANDs if you need an ANDed query instead).

The last part just passes the conditions but not as an array but as the individual elements instead (that's what the * does) because the query now has three parameters instead of one.

Can I find all values LIKE ["_bc","a_c","ab_"]

2 Answers2