Someone had an issue today where they were using a string as if it were a regular expression. Not totally understanding what the desired end result would be, I had suggested they use something equivalent to Java’s Pattern.quote() function. It turned out that they really just wanted to determine if one string was equivalent to another, but the issue still made me wonder: is there an equivalent in PostgreSQL which gives you the ability to get a literal regular expression pattern string for any string? I unfortunately didn’t find anything in the documentation pages for PostgreSQL so I wrote the following function definition:

  LANGUAGE plpgsql
AS $$
 * Function Name: regexp_quote
 * In-coming Param:
 *   The string to decoded and convert into a set of text arrays.
 * Returns:
 *   This function produces a TEXT that can be used as a regular expression
 *   pattern that would match the input as if it were a literal pattern.
 * Description:
 *   Takes in a TEXT in and escapes all of the necessary characters so that
 *   the output can be used as a regular expression to match the input as if
 *   it were a literal pattern.
 * Created On: 2012-07-10
 * Updated On: 2012-07-10
 * Author: Chris West
  RETURN REGEXP_REPLACE($1, '([[\\](){}.+*^$|\\\\?-])', '\\\\\\1', 'g');

This function can be in the following way:

SELECT regexp_quote('[]{}'); -- produces "\\[\\]\\{\\}"

You really don’t have to define a function if you don’t want to. You could just use the following form to do the equivalent:

-- produce "\\[\\]\\{\\}"
SELECT REGEXP_REPLACE('[]{}', '([[\\](){}.+*^$|\\\\?-])', '\\\\\\1', 'g');

1 Comment

Gilad · March 9, 2017 at 5:37 AM

‘ERROR: invalid regular expression: quantifier operand invalid’
For PostgreSQL 9.5.1.
I’m guessing the function is probably outdated.

Leave a Reply

Your email address will not be published. Required fields are marked *