2

I have the following query:

SELECT
   field
FROM
   myTable
WHERE
   field ~ '\\Qprefix[\\E.+'

It won't find values like prefix[foo].

How can I replace \Q..\E ?

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
Stephan
  • 41,764
  • 65
  • 238
  • 329
  • 1
    What exactly does `\Q` do? –  Jan 23 '14 at 16:35
  • @a_horse_with_no_name _"Many flavors also support the `\Q…\E` escape sequence. All the characters between the `\Q` and the `\E` are interpreted as literal characters."_ http://www.regular-expressions.info/characters.html – Wiseguy Jan 23 '14 at 16:38
  • 1
    you can use "Regular Expression Metasyntax". Normally the flavor of RE being used is determined by regex_flavor. However, this can be overridden by a director prefix. If an RE begins with ***:, the rest of the RE is taken as an ARE regardless of regex_flavor. If an RE begins with ***=, the rest of the RE is taken to be a literal string, with all characters considered ordinary characters. Refer to http://www.postgresql.org/docs/8.3/static/functions-matching.html – Danila Ladner Jan 23 '14 at 16:45
  • Perl's `\Q .. \E` syntax is equivalent to find `([^\w])`, replace with `\\$1`. Maybe you could do it by hand if constants. If variables, maybe you could do a separate regex on the variable if your language supports it. –  Jan 23 '14 at 17:54
  • For example, `prefix\[.+` –  Jan 23 '14 at 17:57
  • 1
    @DanilaLadner Can you provide a sample code? – Stephan Jan 24 '14 at 10:20

1 Answers1

1

This form of regex with a \Q..\E unquoted substring is only supported by PCRE, which is not available natively in PostgreSQL.

If your program must deal with this syntax in general, the PCRE support can be installed as an extension, as provided here: https://github.com/petere/pgpcre

On the other hand, if it's only that one regex that should be made to work, first note that the double backslashes in '\\Qprefix[\\E.+' means literally two backslashes with PostgreSQL 9.1 and above, unless standard_conforming_strings is explicitly switched to OFF. To be insensitive to this setting, literals with the old syntax are expected to be prefixed with E. This is described in String Constants with C-style Escapes in the doc.

To simply match prefix[foo] with a PostgreSQL-style regex with the modern syntax, this works:

test=> show standard_conforming_strings ;
 standard_conforming_strings 
-----------------------------
 on
(1 row)

test=> select 'prefix[foo]' ~ 'prefix\[.+';
 ?column? 
----------
 t
(1 row)
Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156