How to treat part of a regex as a literal string on postgresql?

Question

I have a piece of code like this:

Foo.where("name ~* '.*#{string}.*'")

Where string has both word characters and non-word characters and if some of them are not escaped I get invalid Regular Expressions errors.

So I need to get postgreslq to take string as a literal or escape the meta-characters used by the regex. Is there a way to do it instead of getting a list of all meta-characters and adding the backslash?

---

I have looked at both these questions but none of the answers is exactly what I'm looking for:

• Proper way to add unescaped text from a field to a regex in postgres?

Here the accepted answer escapes all non-word characters. And using that with my strings I am afraid there will be weird behaviors with escaped non-word characters.

• What is the equivalent of regexp \Q...\E in postgresql?

Here the answer is specific to the question and can't be used in my case.

Answer 1

I think you can just use Ruby's Regexp.escape() to espace characters in string that have a special meaning in a regular expression.

For example

> string = 'C++ /?|foo bar baz'
=> "C++ /?|"
> Regexp.escape(string)
=> "C\\+\\+\\ /\\?\\|foo\\ bar\\ baz"

With this in mind you would write

Foo.where("name ~* '.*#{Regexp.escape(string)}.*'")

_{Note: this is untested.}

Answer 2

You could just plainly convert all characters of string to the ASCII or Unicode hex number and use the \xhh or \x{hhhh}. Should easy to code and should not have side-effects.