- publishing free software manuals
The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 454 pages
ISBN 9781906966041
RRP £14.95 ($19.95)

Sales of this book support the PostgreSQL project! Get a printed copy>>>

7.7.3.3 Regular Expression Escapes

Escapes are special sequences beginning with \ followed by an alphanumeric character. Escapes come in several varieties: character entry, class shorthands, constraint escapes, and back references. A \ followed by an alphanumeric character but not constituting a valid escape is illegal in AREs. In EREs, there are no escapes: outside a bracket expression, a \ followed by an alphanumeric character merely stands for that character as an ordinary character, and inside a bracket expression, \ is an ordinary character. (The latter is the one actual incompatibility between EREs and AREs.)

Character-entry escapes exist to make it easier to specify non-printing and other inconvenient characters in REs. They are shown in Table 7-15.

Class-shorthand escapes provide shorthands for certain commonly-used character classes. They are shown in Table 7-16.

A constraint escape is a constraint, matching the empty string if specific conditions are met, written as an escape. They are shown in Table 7-17.

A back reference (\n) matches the same string matched by the previous parenthesized subexpression specified by the number n (see Table 7-18). For example, ([bc])\1 matches bb or cc but not bc or cb. The subexpression must entirely precede the back reference in the RE. Subexpressions are numbered in the order of their leading parentheses. Non-capturing parentheses do not define subexpressions.

Note: Keep in mind that an escape's leading \ will need to be doubled when entering the pattern as an SQL string constant. For example:

'123' ~ E'^\\d{3}' true
Table 7-15: Regular Expression Character-Entry Escapes
Escape Description
\a alert (bell) character, as in C
\b backspace, as in C
\B synonym for backslash (\) to help reduce the need for backslash doubling
\cX (where X is any character) the character whose low-order 5 bits are the same as those of X, and whose other bits are all zero
\e the character whose collating-sequence name is ESC, or failing that, the character with octal value 033
\f form feed, as in C
\n newline, as in C
\r carriage return, as in C
\t horizontal tab, as in C
\uwxyz (where wxyz is exactly four hexadecimal digits) the UTF16 (Unicode, 16-bit) character U+wxyz in the local byte ordering
\Ustuvwxyz (where stuvwxyz is exactly eight hexadecimal digits) reserved for a hypothetical Unicode extension to 32 bits
\v vertical tab, as in C
\xhhh (where hhh is any sequence of hexadecimal digits) the character whose hexadecimal value is 0xhhh (a single character no matter how many hexadecimal digits are used)
\0 the character whose value is 0 (the null byte)
\xy (where xy is exactly two octal digits, and is not a back reference) the character whose octal value is 0xy
\xyz (where xyz is exactly three octal digits, and is not a back reference) the character whose octal value is 0xyz

Hexadecimal digits are 0-9, a-f, and A-F. Octal digits are 0-7.

The character-entry escapes are always taken as ordinary characters. For example, \135 is ] in ASCII, but \135 does not terminate a bracket expression.

Table 7-16: Regular Expression Class-Shorthand Escapes
Escape Description
\d [[:digit:]]
\s [[:space:]]
\w [[:alnum:]_] (note underscore is included)
\D [^[:digit:]]
\S [^[:space:]]
\W [^[:alnum:]_] (note underscore is included)

Within bracket expressions, \d, \s, and \w lose their outer brackets, and \D, \S, and \W are illegal. (So, for example, [a-c\d] is equivalent to [a-c[:digit:]]. Also, [a-c\D], which is equivalent to [a-c^[:digit:]], is illegal.)

Table 7-17: Regular Expression Constraint Escapes
Escape Description
\A matches only at the beginning of the string (see section 7.7.3.5 Regular Expression Matching Rules for how this differs from ^)
\m matches only at the beginning of a word
\M matches only at the end of a word
\y matches only at the beginning or end of a word
\Y matches only at a point that is not the beginning or end of a word
\Z matches only at the end of the string (see section 7.7.3.5 Regular Expression Matching Rules for how this differs from $)

A word is defined as in the specification of [[:<:]] and [[:>:]] above. Constraint escapes are illegal within bracket expressions.

Table 7-18: Regular Expression Back References
Escape Description
\m (where m is a nonzero digit) a back reference to the m'th subexpression
\mnn (where m is a nonzero digit, and nn is some more digits, and the decimal value mnn is not greater than the number of closing capturing parentheses seen so far) a back reference to the mnn'th subexpression

Note: There is an inherent ambiguity between octal character-entry escapes and back references, which is resolved by the following heuristics, as hinted at above. A leading zero always indicates an octal escape. A single non-zero digit, not followed by another digit, is always taken as a back reference. A multi-digit sequence not starting with a zero is taken as a back reference if it comes after a suitable subexpression (i.e., the number is in the legal range for a back reference), and otherwise is taken as octal.

ISBN 9781906966041The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language ReferenceSee the print edition