Movatterモバイル変換

Note

If you havestandard_conforming_strings turned off, any backslashes you write in literal string constants will need to be doubled. SeeSection 4.1.2.1 for more information.

• | denotes alternation (either of two alternatives).

• * denotes repetition of the previous item zero or more times.

• + denotes repetition of the previous item one or more times.

• ? denotes repetition of the previous item zero or one time.

• {m} denotes repetition of the previous item exactlym times.

• {m,} denotes repetition of the previous itemm or more times.

• {m,n} denotes repetition of the previous item at leastm and not more thann times.

• Parentheses() can be used to group items into a single logical item.

• A bracket expression[...] specifies a character class, just as in POSIX regular expressions.

Table 9.16. Regular Expression Match Operators

Tip

In the common case where you just want the whole matching substring orNULL for no match, the best solution is to useregexp_substr(). However,regexp_substr() only exists inPostgreSQL version 15 and up. When working in older versions, you can extract the first element ofregexp_match()'s result, for example:

SELECT (regexp_match('foobarbequebaz', 'bar.*que'))[1]; regexp_match-------------- barbeque(1 row)

Tip

In most casesregexp_matches() should be used with theg flag, since if you only want the first match, it's easier and more efficient to useregexp_match(). However,regexp_match() only exists inPostgres Pro version 10 and up. When working in older versions, a common trick is to place aregexp_matches() call in a sub-select, for example:

SELECT col1, (SELECT regexp_matches(col2, '(bar)(beque)')) FROM tab;

This produces a text array if there's a match, orNULL if not, the same asregexp_match() would do. Without the sub-select, this query would produce no output at all for table rows without a match, which is typically not the desired behavior.

Postgres Pro's regular expressions are implemented using a software package written by Henry Spencer. Much of the description of regular expressions below is copied verbatim from his manual.

Regular expressions (REs), as defined inPOSIX 1003.2, come in two forms:extendedREs orEREs (roughly those ofegrep), andbasicREs orBREs (roughly those ofed).Postgres Pro supports both forms, and also implements some extensions that are not in the POSIX standard, but have become widely used due to their availability in programming languages such as Perl and Tcl.REs using these non-POSIX extensions are calledadvancedREs orAREs in this documentation. AREs are almost an exact superset of EREs, but BREs have several notational incompatibilities (as well as being much more limited). We first describe the ARE and ERE forms, noting features that apply only to AREs, and then describe how BREs differ.

A regular expression is defined as one or morebranches, separated by|. It matches anything that matches one of the branches.

A branch is zero or morequantified atoms orconstraints, concatenated. It matches a match for the first, followed by a match for the second, etc.; an empty branch matches the empty string.

A quantified atom is anatom possibly followed by a singlequantifier. Without a quantifier, it matches a match for the atom. With a quantifier, it can match some number of matches of the atom. Anatom can be any of the possibilities shown inTable 9.17. The possible quantifiers and their meanings are shown inTable 9.18.

Aconstraint matches an empty string, but matches only when specific conditions are met. A constraint can be used where an atom could be used, except it cannot be followed by a quantifier. The simple constraints are shown inTable 9.19; some more constraints are described later.

An RE cannot end with a backslash (\).

The forms using{...} are known asbounds. The numbersm andn within a bound are unsigned decimal integers with permissible values from 0 to 255 inclusive.

Non-greedy quantifiers (available in AREs only) match the same possibilities as their corresponding normal (greedy) counterparts, but prefer the smallest number rather than the largest number of matches. SeeSection 9.7.3.5 for more detail.

Lookahead and lookbehind constraints cannot containback references (seeSection 9.7.3.3), and all parentheses within them are considered non-capturing.

Abracket expression is a list of characters enclosed in[]. It normally matches any single character from the list (but see below). If the list begins with^, it matches any single characternot from the rest of the list. If two characters in the list are separated by-, this is shorthand for the full range of characters between those two (inclusive) in the collating sequence, e.g.,[0-9] inASCII matches any decimal digit. It is illegal for two ranges to share an endpoint, e.g.,a-c-e. Ranges are very collating-sequence-dependent, so portable programs should avoid relying on them.

To include a literal] in the list, make it the first character (after^, if that is used). To include a literal-, make it the first or last character, or the second endpoint of a range. To use a literal- as the first endpoint of a range, enclose it in[. and.] to make it a collating element (see below). With the exception of these characters, some combinations using[ (see next paragraphs), and escapes (AREs only), all other special characters lose their special significance within a bracket expression. In particular,\ is not special when following ERE or BRE rules, though it is special (as introducing an escape) in AREs.

Within a bracket expression, a collating element (a character, a multiple-character sequence that collates as if it were a single character, or a collating-sequence name for either) enclosed in[. and.] stands for the sequence of characters of that collating element. The sequence is treated as a single element of the bracket expression's list. This allows a bracket expression containing a multiple-character collating element to match more than one character, e.g., if the collating sequence includes ach collating element, then the RE[[.ch.]]*c matches the first five characters ofchchcc.

Within a bracket expression, a collating element enclosed in[= and=] is anequivalence class, standing for the sequences of characters of all collating elements equivalent to that one, including itself. (If there are no other equivalent collating elements, the treatment is as if the enclosing delimiters were[. and.].) For example, ifo and^ are the members of an equivalence class, then[[=o=]],[[=^=]], and[o^] are all synonymous. An equivalence class cannot be an endpoint of a range.

Within a bracket expression, the name of a character class enclosed in[: and:] stands for the list of all characters belonging to that class. A character class cannot be used as an endpoint of a range. ThePOSIX standard defines these character class names:alnum (letters and numeric digits),alpha (letters),blank (space and tab),cntrl (control characters),digit (numeric digits),graph (printable characters except space),lower (lower-case letters),print (printable characters including space),punct (punctuation),space (any white space),upper (upper-case letters), andxdigit (hexadecimal digits). The behavior of these standard character classes is generally consistent across platforms for characters in the 7-bit ASCII set. Whether a given non-ASCII character is considered to belong to one of these classes depends on thecollation that is used for the regular-expression function or operator (seeSection 22.2), or by default on the database'sLC_CTYPE locale setting (seeSection 22.1). The classification of non-ASCII characters can vary across platforms even in similarly-named locales. (But theC locale never considers any non-ASCII characters to belong to any of these classes.) In addition to these standard character classes,Postgres Pro defines theword character class, which is the same asalnum plus the underscore (_) character, and theascii character class, which contains exactly the 7-bit ASCII set.

There are two special cases of bracket expressions: the bracket expressions[[:<:]] and[[:>:]] are constraints, matching empty strings at the beginning and end of a word respectively. A word is defined as a sequence of word characters that is neither preceded nor followed by word characters. A word character is any character belonging to theword character class, that is, any letter, digit, or underscore. This is an extension, compatible with but not specified byPOSIX 1003.2, and should be used with caution in software intended to be portable to other systems. The constraint escapes described below are usually preferable; they are no more standard, but are easier to type.

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 inTable 9.20.

Class-shorthand escapes provide shorthands for certain commonly-used character classes. They are shown inTable 9.21.

Aconstraint escape is a constraint, matching the empty string if specific conditions are met, written as an escape. They are shown inTable 9.22.

Aback reference (\n) matches the same string matched by the previous parenthesized subexpression specified by the numbern (seeTable 9.23). For example,([bc])\1 matchesbb orcc but notbc orcb. 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. The back reference considers only the string characters matched by the referenced subexpression, not any constraints contained in it. For example,(^\d)\1 will match22.

Hexadecimal digits are0-9,a-f, andA-F. Octal digits are0-7.

Numeric character-entry escapes specifying values outside the ASCII range (0–127) have meanings dependent on the database encoding. When the encoding is UTF-8, escape values are equivalent to Unicode code points, for example\u1234 means the characterU+1234. For other multibyte encodings, character-entry escapes usually just specify the concatenation of the byte values for the character. If the escape value does not correspond to any legal character in the database encoding, no error will be raised, but it will never match any data.

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

The class-shorthand escapes also work within bracket expressions, although the definitions shown above are not quite syntactically valid in that context. For example,[a-c\d] is equivalent to[a-c[:digit:]].

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

In addition to the main syntax described above, there are some special forms and miscellaneous syntactic facilities available.

An RE can begin with one of two specialdirector prefixes. If an RE begins with***:, the rest of the RE is taken as an ARE. (This normally has no effect inPostgres Pro, since REs are assumed to be AREs; but it does have an effect if ERE or BRE mode had been specified by theflags parameter to a regex function.) If an RE begins with***=, the rest of the RE is taken to be a literal string, with all characters considered ordinary characters.

An ARE can begin withembedded options: a sequence(?xyz) (wherexyz is one or more alphabetic characters) specifies options affecting the rest of the RE. These options override any previously determined options — in particular, they can override the case-sensitivity behavior implied by a regex operator, or theflags parameter to a regex function. The available option letters are shown inTable 9.24. Note that these same option letters are used in theflags parameters of regex functions.

Embedded options take effect at the) terminating the sequence. They can appear only at the start of an ARE (after the***: director if any).

In addition to the usual (tight) RE syntax, in which all characters are significant, there is anexpanded syntax, available by specifying the embeddedx option. In the expanded syntax, white-space characters in the RE are ignored, as are all characters between a# and the following newline (or the end of the RE). This permits paragraphing and commenting a complex RE. There are three exceptions to that basic rule:

For this purpose, white-space characters are blank, tab, newline, and any character that belongs to thespace character class.

Finally, in an ARE, outside bracket expressions, the sequence(?#ttt) (wherettt is any text not containing a)) is a comment, completely ignored. Again, this is not allowed between the characters of multi-character symbols, like(?:. Such comments are more a historical artifact than a useful facility, and their use is deprecated; use the expanded syntax instead.

None of these metasyntax extensions is available if an initial***= director has specified that the user's input be treated as a literal string rather than as an RE.

In the event that an RE could match more than one substring of a given string, the RE matches the one starting earliest in the string. If the RE could match more than one substring starting at that point, either the longest possible match or the shortest possible match will be taken, depending on whether the RE isgreedy ornon-greedy.

Whether an RE is greedy or not is determined by the following rules:

The above rules associate greediness attributes not only with individual quantified atoms, but with branches and entire REs that contain quantified atoms. What that means is that the matching is done in such a way that the branch, or whole RE, matches the longest or shortest possible substringas a whole. Once the length of the entire match is determined, the part of it that matches any particular subexpression is determined on the basis of the greediness attribute of that subexpression, with subexpressions starting earlier in the RE taking priority over ones starting later.

An example of what this means:

SELECT SUBSTRING('XY1234Z', 'Y*([0-9]{1,3})');Result:123SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');Result:1

In the first case, the RE as a whole is greedy becauseY* is greedy. It can match beginning at theY, and it matches the longest possible string starting there, i.e.,Y123. The output is the parenthesized part of that, or123. In the second case, the RE as a whole is non-greedy becauseY*? is non-greedy. It can match beginning at theY, and it matches the shortest possible string starting there, i.e.,Y1. The subexpression[0-9]{1,3} is greedy but it cannot change the decision as to the overall match length; so it is forced to match just1.

In short, when an RE contains both greedy and non-greedy subexpressions, the total match length is either as long as possible or as short as possible, according to the attribute assigned to the whole RE. The attributes assigned to the subexpressions only affect how much of that match they are allowed to“eat” relative to each other.

The quantifiers{1,1} and{1,1}? can be used to force greediness or non-greediness, respectively, on a subexpression or a whole RE. This is useful when you need the whole RE to have a greediness attribute different from what's deduced from its elements. As an example, suppose that we are trying to separate a string containing some digits into the digits and the parts before and after them. We might try to do that like this:

SELECT regexp_match('abc01234xyz', '(.*)(\d+)(.*)');Result:{abc0123,4,xyz}

That didn't work: the first.* is greedy so it“eats” as much as it can, leaving the\d+ to match at the last possible place, the last digit. We might try to fix that by making it non-greedy:

SELECT regexp_match('abc01234xyz', '(.*?)(\d+)(.*)');Result:{abc,0,""}

That didn't work either, because now the RE as a whole is non-greedy and so it ends the overall match as soon as possible. We can get what we want by forcing the RE as a whole to be greedy:

SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');Result:{abc,01234,xyz}

Controlling the RE's overall greediness separately from its components' greediness allows great flexibility in handling variable-length patterns.

When deciding what is a longer or shorter match, match lengths are measured in characters, not collating elements. An empty string is considered longer than no match at all. For example:bb* matches the three middle characters ofabbbc;(week|wee)(night|knights) matches all ten characters ofweeknights; when(.*).* is matched againstabc the parenthesized subexpression matches all three characters; and when(a*)* is matched againstbc both the whole RE and the parenthesized subexpression match an empty string.

If case-independent matching is specified, the effect is much as if all case distinctions had vanished from the alphabet. When an alphabetic that exists in multiple cases appears as an ordinary character outside a bracket expression, it is effectively transformed into a bracket expression containing both cases, e.g.,x becomes[xX]. When it appears inside a bracket expression, all case counterparts of it are added to the bracket expression, e.g.,[x] becomes[xX] and[^x] becomes[^xX].

If newline-sensitive matching is specified,. and bracket expressions using^ will never match the newline character (so that matches will not cross lines unless the RE explicitly includes a newline) and^ and$ will match the empty string after and before a newline respectively, in addition to matching at beginning and end of string respectively. But the ARE escapes\A and\Z continue to match beginning or end of stringonly. Also, the character class shorthands\D and\W will match a newline regardless of this mode. (BeforePostgres Pro 14, they did not match newlines when in newline-sensitive mode. Write[^[:digit:]] or[^[:word:]] to get the old behavior.)

If partial newline-sensitive matching is specified, this affects. and bracket expressions as with newline-sensitive matching, but not^ and$.

If inverse partial newline-sensitive matching is specified, this affects^ and$ as with newline-sensitive matching, but not. and bracket expressions. This isn't very useful but is provided for symmetry.

No particular limit is imposed on the length of REs in this implementation. However, programs intended to be highly portable should not employ REs longer than 256 bytes, as a POSIX-compliant implementation can refuse to accept such REs.

The only feature of AREs that is actually incompatible with POSIX EREs is that\ does not lose its special significance inside bracket expressions. All other ARE features use syntax which is illegal or has undefined or unspecified effects in POSIX EREs; the*** syntax of directors likewise is outside the POSIX syntax for both BREs and EREs.

Many of the ARE extensions are borrowed from Perl, but some have been changed to clean them up, and a few Perl extensions are not present. Incompatibilities of note include\b,\B, the lack of special treatment for a trailing newline, the addition of complemented bracket expressions to the things affected by newline-sensitive matching, the restrictions on parentheses and back references in lookahead/lookbehind constraints, and the longest/shortest-match (rather than first-match) matching semantics.

BREs differ from EREs in several respects. In BREs,|,+, and? are ordinary characters and there is no equivalent for their functionality. The delimiters for bounds are\{ and\}, with{ and} by themselves ordinary characters. The parentheses for nested subexpressions are\( and\), with( and) by themselves ordinary characters.^ is an ordinary character except at the beginning of the RE or the beginning of a parenthesized subexpression,$ is an ordinary character except at the end of the RE or the end of a parenthesized subexpression, and* is an ordinary character if it appears at the beginning of the RE or the beginning of a parenthesized subexpression (after a possible leading^). Finally, single-digit back references are available, and\< and\> are synonyms for[[:<:]] and[[:>:]] respectively; no other escapes are available in BREs.

Since SQL:2008, the SQL standard includes regular expression operators and functions that performs pattern matching according to the XQuery regular expression standard:

Postgres Pro does not currently implement these operators and functions. You can get approximately equivalent functionality in each case as shown inTable 9.25. (Various optional clauses on both sides have been omitted in this table.)

Regular expression functions similar to those provided by PostgreSQL are also available in a number of other SQL implementations, whereas the SQL-standard functions are not as widely implemented. Some of the details of the regular expression syntax will likely differ in each implementation.

The SQL-standard operators and functions use XQuery regular expressions, which are quite close to the ARE syntax described above. Notable differences between the existing POSIX-based regular-expression feature and XQuery regular expressions include: