Escape function for regular expression or LIKE patterns

by

To address the question at the top:

Assuming standard_conforming_strings = on, like it’s default since Postgres 9.1.

Regular expression escape function

Let’s start with a complete list of characters with special meaning in regular expression patterns:

!$()*+.:<=>?[\]^{|}-

Wrapped in a bracket expression most of them lose their special meaning – with a few exceptions:

  • - needs to be first or last or it signifies a range of characters.
  • ] and \ have to be escaped with \ (in the replacement, too).

After adding capturing parentheses for the back reference below we get this regexp pattern:

([!$()*+.:<=>?[\\\]^{|}-])

Using it, this function escapes all special characters with a backslash (\) – thereby removing the special meaning:

CREATE OR REPLACE FUNCTION f_regexp_escape(text)
  RETURNS text
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
$func$
SELECT regexp_replace($1, '([!$()*+.:<=>?[\\\]^{|}-])', '\\\1', 'g')
$func$;

Add PARALLEL SAFE (because it is) in Postgres 10 or later to allow parallelism for queries using it.

Demo

SELECT f_regexp_escape('test(1) > Foo*');

Returns:

test\(1\) \> Foo\*

And while:

SELECT 'test(1) > Foo*' ~ 'test(1) > Foo*';

returns FALSE, which may come as a surprise to naive users,

SELECT 'test(1) > Foo*' ~ f_regexp_escape('test(1) > Foo*');

Returns TRUE as it should now.

LIKE escape function

For completeness, the pendant for LIKE patterns, where only three characters are special:

\%_

The manual:

The default escape character is the backslash but a different one can be selected by using the ESCAPE clause.

This function assumes the default:

CREATE OR REPLACE FUNCTION f_like_escape(text)
  RETURNS text
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
$func$
SELECT replace(replace(replace($1
         , '\', '\\')  -- must come 1st
         , '%', '\%')
         , '_', '\_');
$func$;

We could use the more elegant regexp_replace() here, too, but for the few characters, a cascade of replace() functions is faster.

Again, PARALLEL SAFE in Postgres 10 or later.

Demo

SELECT f_like_escape('20% \ 50% low_prices');

Returns:

20\% \\ 50\% low\_prices

More Related Contents:

  1. Regular expression to match a line that doesn’t contain a word
  2. In regex, match either the end of the string or a specific character
  3. Regex for quoted string with escaping quotes
  4. isnumeric() with PostgreSQL
  5. Escaping a forward slash in a regular expression
  6. powershell: how to escape all regex characters from a string
  7. PostgreSQL Regex Word Boundaries?
  8. Regular expression in PostgreSQL LIKE clause
  9. Case insensitive search in Mongo
  10. How can I match double-quoted strings with escaped double-quote characters?
  11. notepad++ Inverse Regex replace (all but string)
  12. Grep and regex – why am I escaping curly braces?
  13. Regex: ignore case sensitivity
  14. What is the ultimate postal code and zip regex?
  15. Using the star sign in grep
  16. Regex group capture in R with multiple capture-groups
  17. List of all characters that should be escaped before put in to RegEx?
  18. Execute a dynamic crosstab query
  19. Regular expression – starting and ending with a character string
  20. To use or not to use regular expressions?
  21. Multiple regex matches in Google Sheets formula
  22. Regex: How to match a string that is not only numbers
  23. How do I get regex support in Excel via a function, or custom function?
  24. Combining $regex and $or operators in Mongo
  25. Why does VIM have its own regex syntax?
  26. Replace with whole match value using Notepad++ regex search and replace
  27. Replace all whitespace with a line break/paragraph mark to make a word list
  28. Why do regex engines allow / automatically attempt matching at the end of the input string?
  29. How do I turn any regex into an complement of itself without complex hand editing?
  30. Apache mod_rewrite REDIRECT_STATUS condition causing directory listing

Leave a Comment