PostgreSQL – Escape Regular Expressions

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:

CREATE OR REPLACE FUNCTION public.regexp_quote(IN TEXT)
  RETURNS TEXT
  LANGUAGE plpgsql
  STABLE
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
 ******************************************************************************/
BEGIN
  RETURN REGEXP_REPLACE($1, '([[\\](){}.+*^$|\\\\?-])', '\\\\\\1', 'g');
END;
$$

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');