Last Updated: February 25, 2016
·
1.987K
· manudwarf

Convert SQL `LIKE` wildcards to Ruby RegExp

Hey guys,

I had this weird case where a SQL search had to be completed with a data array already present in memory.

The data were (in both DB and memory) stored in key/value, so it was pretty easy until I was asked to handle SQL wildcards...

Eventually it wasn't that complicated :

configs = {}
Model.where("key like #{key}").each do |c|
  configs[c.key] = c.value
end

pattern = Regexp.escape key
pattern = pattern.sub "%", ".*"
pattern = pattern.sub "\\?", "."
pattern = "^#{pattern}$"
r = Regexp.new pattern

opts = whateverYourDataComeFrom
opts.each do |k, v|
  if r.match k
    configs[k] = v
  end
end

A couple of things to notice :

  • Regexp.escape is very useful to prevent special characters to be interpreted as regexp instructions
  • % becomes .*, literally "any character in any amount"
  • ? becomes ., literally "any character, just once". Be careful, ? has a meaning in regexp and is escaped as \\?
  • ^...$ for "string beginning" and "string ending"

Here it is, enjoy !

3 Responses
Add your response

Just curious here.. what DB are you using? Wouldn't it be the case of matching directly against a regex? I know it is somehow possible with PostgreSQL ...

over 1 year ago ·

Well, seems so :D

I didn't know for psql, thanks for the advice !

over 1 year ago ·

Great post, very helpful, thank you. And by the way, greeetings from France from a fellow French coder !

frogFangio

over 1 year ago ·