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.

See the following answer to a similar question: stackoverflow.com/a/45741630 — Deven T. Corzine
– Deven T. Corzine, Commented Jun 4, 2021 at 4:13

Gonçalo Ribeiro · Accepted Answer · 2015-09-10 17:21:47Z

2

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.}

answered Sep 10, 2015 at 17:21

Gonçalo Ribeiro

2161 silver badge13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

azt · Accepted Answer · 2015-09-10 15:16:28Z

1

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.

answered Sep 10, 2015 at 15:16

azt

2,13016 silver badges25 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related