Baoding-FenShuaJiang
/
Green_GoodERP18_FSJ_Standard
Observar 1
Favorito 0
Wiki 捐赠
gooderp18绿色标准版
Você não pode selecionar mais de 25 tópicos Os tópicos devem começar com uma letra ou um número, podem incluir traços ('-') e podem ter até 35 caracteres.
Branch: master
Branches Tags
${ item.name }
Criar branch ${ searchTerm }
de master
${ noResults }
Green_GoodERP18_FSJ_Standard/PostgreSQL/doc/postgresql/html/functions-matching.html

1176 linhas
92KB
Original Link permanente Anotar Histórico 

  1. <?xml version="1.0" encoding="UTF-8" standalone="no"?>

  2. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>9.7. Pattern Matching</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets V1.79.1" /><link rel="prev" href="functions-bitstring.html" title="9.6. Bit String Functions and Operators" /><link rel="next" href="functions-formatting.html" title="9.8. Data Type Formatting Functions" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">9.7. Pattern Matching</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-bitstring.html" title="9.6. Bit String Functions and Operators">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><th width="60%" align="center">Chapter 9. Functions and Operators</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 12.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-formatting.html" title="9.8. Data Type Formatting Functions">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="FUNCTIONS-MATCHING"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.7. Pattern Matching</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="functions-matching.html#FUNCTIONS-LIKE">9.7.1. <code class="function">LIKE</code></a></span></dt><dt><span class="sect2"><a href="functions-matching.html#FUNCTIONS-SIMILARTO-REGEXP">9.7.2. <code class="function">SIMILAR TO</code> Regular Expressions</a></span></dt><dt><span class="sect2"><a href="functions-matching.html#FUNCTIONS-POSIX-REGEXP">9.7.3. <acronym class="acronym">POSIX</acronym> Regular Expressions</a></span></dt></dl></div><a id="id-1.5.8.12.2" class="indexterm"></a><p>

  3.     There are three separate approaches to pattern matching provided

  4.     by <span class="productname">PostgreSQL</span>: the traditional

  5.     <acronym class="acronym">SQL</acronym> <code class="function">LIKE</code> operator, the

  6.     more recent <code class="function">SIMILAR TO</code> operator (added in

  7.     SQL:1999), and <acronym class="acronym">POSIX</acronym>-style regular

  8.     expressions.  Aside from the basic <span class="quote">“<span class="quote">does this string match

  9.     this pattern?</span>”</span> operators, functions are available to extract

  10.     or replace matching substrings and to split a string at matching

  11.     locations.

  12.    </p><div class="tip"><h3 class="title">Tip</h3><p>

  13.      If you have pattern matching needs that go beyond this,

  14.      consider writing a user-defined function in Perl or Tcl.

  15.     </p></div><div class="caution"><h3 class="title">Caution</h3><p>

  16.      While most regular-expression searches can be executed very quickly,

  17.      regular expressions can be contrived that take arbitrary amounts of

  18.      time and memory to process.  Be wary of accepting regular-expression

  19.      search patterns from hostile sources.  If you must do so, it is

  20.      advisable to impose a statement timeout.

  21.     </p><p>

  22.      Searches using <code class="function">SIMILAR TO</code> patterns have the same

  23.      security hazards, since <code class="function">SIMILAR TO</code> provides many

  24.      of the same capabilities as <acronym class="acronym">POSIX</acronym>-style regular

  25.      expressions.

  26.     </p><p>

  27.      <code class="function">LIKE</code> searches, being much simpler than the other

  28.      two options, are safer to use with possibly-hostile pattern sources.

  29.     </p></div><p>

  30.     The pattern matching operators of all three kinds do not support

  31.     nondeterministic collations.  If required, apply a different collation to

  32.     the expression to work around this limitation.

  33.    </p><div class="sect2" id="FUNCTIONS-LIKE"><div class="titlepage"><div><div><h3 class="title">9.7.1. <code class="function">LIKE</code></h3></div></div></div><a id="id-1.5.8.12.7.2" class="indexterm"></a><pre class="synopsis">

  34. <em class="replaceable"><code>string</code></em> LIKE <em class="replaceable"><code>pattern</code></em> [<span class="optional">ESCAPE <em class="replaceable"><code>escape-character</code></em></span>]

  35. <em class="replaceable"><code>string</code></em> NOT LIKE <em class="replaceable"><code>pattern</code></em> [<span class="optional">ESCAPE <em class="replaceable"><code>escape-character</code></em></span>]

  36. </pre><p>

  37.      The <code class="function">LIKE</code> expression returns true if the

  38.      <em class="replaceable"><code>string</code></em> matches the supplied

  39.      <em class="replaceable"><code>pattern</code></em>.  (As

  40.      expected, the <code class="function">NOT LIKE</code> expression returns

  41.      false if <code class="function">LIKE</code> returns true, and vice versa.

  42.      An equivalent expression is

  43.      <code class="literal">NOT (<em class="replaceable"><code>string</code></em> LIKE

  44.       <em class="replaceable"><code>pattern</code></em>)</code>.)

  45.     </p><p>

  46.      If <em class="replaceable"><code>pattern</code></em> does not contain percent

  47.      signs or underscores, then the pattern only represents the string

  48.      itself; in that case <code class="function">LIKE</code> acts like the

  49.      equals operator.  An underscore (<code class="literal">_</code>) in

  50.      <em class="replaceable"><code>pattern</code></em> stands for (matches) any single

  51.      character; a percent sign (<code class="literal">%</code>) matches any sequence

  52.      of zero or more characters.

  53.     </p><p>

  54.     Some examples:

  55. </p><pre class="programlisting">

  56. 'abc' LIKE 'abc'    <em class="lineannotation"><span class="lineannotation">true</span></em>

  57. 'abc' LIKE 'a%'     <em class="lineannotation"><span class="lineannotation">true</span></em>

  58. 'abc' LIKE '_b_'    <em class="lineannotation"><span class="lineannotation">true</span></em>

  59. 'abc' LIKE 'c'      <em class="lineannotation"><span class="lineannotation">false</span></em>

  60. </pre><p>

  61.    </p><p>

  62.     <code class="function">LIKE</code> pattern matching always covers the entire

  63.     string.  Therefore, if it's desired to match a sequence anywhere within

  64.     a string, the pattern must start and end with a percent sign.

  65.    </p><p>

  66.     To match a literal underscore or percent sign without matching

  67.     other characters, the respective character in

  68.     <em class="replaceable"><code>pattern</code></em> must be

  69.     preceded by the escape character.  The default escape

  70.     character is the backslash but a different one can be selected by

  71.     using the <code class="literal">ESCAPE</code> clause.  To match the escape

  72.     character itself, write two escape characters.

  73.    </p><div class="note"><h3 class="title">Note</h3><p>

  74.      If you have <a class="xref" href="runtime-config-compatible.html#GUC-STANDARD-CONFORMING-STRINGS">standard_conforming_strings</a> turned off,

  75.      any backslashes you write in literal string constants will need to be

  76.      doubled.  See <a class="xref" href="sql-syntax-lexical.html#SQL-SYNTAX-STRINGS" title="4.1.2.1. String Constants">Section 4.1.2.1</a> for more information.

  77.     </p></div><p>

  78.     It's also possible to select no escape character by writing

  79.     <code class="literal">ESCAPE ''</code>.  This effectively disables the

  80.     escape mechanism, which makes it impossible to turn off the

  81.     special meaning of underscore and percent signs in the pattern.

  82.    </p><p>

  83.     The key word <code class="token">ILIKE</code> can be used instead of

  84.     <code class="token">LIKE</code> to make the match case-insensitive according

  85.     to the active locale.  This is not in the <acronym class="acronym">SQL</acronym> standard but is a

  86.     <span class="productname">PostgreSQL</span> extension.

  87.    </p><p>

  88.     The operator <code class="literal">~~</code> is equivalent to

  89.     <code class="function">LIKE</code>, and <code class="literal">~~*</code> corresponds to

  90.     <code class="function">ILIKE</code>.  There are also

  91.     <code class="literal">!~~</code> and <code class="literal">!~~*</code> operators that

  92.     represent <code class="function">NOT LIKE</code> and <code class="function">NOT

  93.     ILIKE</code>, respectively.  All of these operators are

  94.     <span class="productname">PostgreSQL</span>-specific.  You may see these

  95.     operator names in <code class="command">EXPLAIN</code> output and similar

  96.     places, since the parser actually translates <code class="function">LIKE</code>

  97.     et al. to these operators.

  98.    </p><p>

  99.     The phrases <code class="function">LIKE</code>, <code class="function">ILIKE</code>,

  100.     <code class="function">NOT LIKE</code>, and <code class="function">NOT ILIKE</code> are

  101.     generally treated as operators

  102.     in <span class="productname">PostgreSQL</span> syntax; for example they can

  103.     be used in <em class="replaceable"><code>expression</code></em>

  104.     <em class="replaceable"><code>operator</code></em> ANY

  105.     (<em class="replaceable"><code>subquery</code></em>) constructs, although

  106.     an <code class="literal">ESCAPE</code> clause cannot be included there.  In some

  107.     obscure cases it may be necessary to use the underlying operator names

  108.     instead.

  109.    </p><p>

  110.     There is also the prefix operator <code class="literal">^@</code> and corresponding

  111.     <code class="function">starts_with</code> function which covers cases when only

  112.     searching by beginning of the string is needed.

  113.    </p></div><div class="sect2" id="FUNCTIONS-SIMILARTO-REGEXP"><div class="titlepage"><div><div><h3 class="title">9.7.2. <code class="function">SIMILAR TO</code> Regular Expressions</h3></div></div></div><a id="id-1.5.8.12.8.2" class="indexterm"></a><a id="id-1.5.8.12.8.3" class="indexterm"></a><a id="id-1.5.8.12.8.4" class="indexterm"></a><pre class="synopsis">

  114. <em class="replaceable"><code>string</code></em> SIMILAR TO <em class="replaceable"><code>pattern</code></em> [<span class="optional">ESCAPE <em class="replaceable"><code>escape-character</code></em></span>]

  115. <em class="replaceable"><code>string</code></em> NOT SIMILAR TO <em class="replaceable"><code>pattern</code></em> [<span class="optional">ESCAPE <em class="replaceable"><code>escape-character</code></em></span>]

  116. </pre><p>

  117.     The <code class="function">SIMILAR TO</code> operator returns true or

  118.     false depending on whether its pattern matches the given string.

  119.     It is similar to <code class="function">LIKE</code>, except that it

  120.     interprets the pattern using the SQL standard's definition of a

  121.     regular expression.  SQL regular expressions are a curious cross

  122.     between <code class="function">LIKE</code> notation and common regular

  123.     expression notation.

  124.    </p><p>

  125.     Like <code class="function">LIKE</code>, the <code class="function">SIMILAR TO</code>

  126.     operator succeeds only if its pattern matches the entire string;

  127.     this is unlike common regular expression behavior where the pattern

  128.     can match any part of the string.

  129.     Also like

  130.     <code class="function">LIKE</code>, <code class="function">SIMILAR TO</code> uses

  131.     <code class="literal">_</code> and <code class="literal">%</code> as wildcard characters denoting

  132.     any single character and any string, respectively (these are

  133.     comparable to <code class="literal">.</code> and <code class="literal">.*</code> in POSIX regular

  134.     expressions).

  135.    </p><p>

  136.     In addition to these facilities borrowed from <code class="function">LIKE</code>,

  137.     <code class="function">SIMILAR TO</code> supports these pattern-matching

  138.     metacharacters borrowed from POSIX regular expressions:

  139. 

  140.    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>

  141.       <code class="literal">|</code> denotes alternation (either of two alternatives).

  142.      </p></li><li class="listitem"><p>

  143.       <code class="literal">*</code> denotes repetition of the previous item zero

  144.       or more times.

  145.      </p></li><li class="listitem"><p>

  146.       <code class="literal">+</code> denotes repetition of the previous item one

  147.       or more times.

  148.      </p></li><li class="listitem"><p>

  149.       <code class="literal">?</code> denotes repetition of the previous item zero

  150.       or one time.

  151.      </p></li><li class="listitem"><p>

  152.       <code class="literal">{</code><em class="replaceable"><code>m</code></em><code class="literal">}</code> denotes repetition

  153.       of the previous item exactly <em class="replaceable"><code>m</code></em> times.

  154.      </p></li><li class="listitem"><p>

  155.       <code class="literal">{</code><em class="replaceable"><code>m</code></em><code class="literal">,}</code> denotes repetition

  156.       of the previous item <em class="replaceable"><code>m</code></em> or more times.

  157.      </p></li><li class="listitem"><p>

  158.       <code class="literal">{</code><em class="replaceable"><code>m</code></em><code class="literal">,</code><em class="replaceable"><code>n</code></em><code class="literal">}</code>

  159.       denotes repetition of the previous item at least <em class="replaceable"><code>m</code></em> and

  160.       not more than <em class="replaceable"><code>n</code></em> times.

  161.      </p></li><li class="listitem"><p>

  162.       Parentheses <code class="literal">()</code> can be used to group items into

  163.       a single logical item.

  164.      </p></li><li class="listitem"><p>

  165.       A bracket expression <code class="literal">[...]</code> specifies a character

  166.       class, just as in POSIX regular expressions.

  167.      </p></li></ul></div><p>

  168. 

  169.     Notice that the period (<code class="literal">.</code>) is not a metacharacter

  170.     for <code class="function">SIMILAR TO</code>.

  171.    </p><p>

  172.     As with <code class="function">LIKE</code>, a backslash disables the special meaning

  173.     of any of these metacharacters; or a different escape character can

  174.     be specified with <code class="literal">ESCAPE</code>.

  175.    </p><p>

  176.     Some examples:

  177. </p><pre class="programlisting">

  178. 'abc' SIMILAR TO 'abc'      <em class="lineannotation"><span class="lineannotation">true</span></em>

  179. 'abc' SIMILAR TO 'a'        <em class="lineannotation"><span class="lineannotation">false</span></em>

  180. 'abc' SIMILAR TO '%(b|d)%'  <em class="lineannotation"><span class="lineannotation">true</span></em>

  181. 'abc' SIMILAR TO '(b|c)%'   <em class="lineannotation"><span class="lineannotation">false</span></em>

  182. </pre><p>

  183.    </p><p>

  184.     The <code class="function">substring</code> function with three parameters

  185.     provides extraction of a substring that matches an SQL

  186.     regular expression pattern.  The function can be written according

  187.     to SQL99 syntax:

  188. </p><pre class="synopsis">

  189. substring(<em class="replaceable"><code>string</code></em> from <em class="replaceable"><code>pattern</code></em> for <em class="replaceable"><code>escape-character</code></em>)

  190. </pre><p>

  191.     or as a plain three-argument function:

  192. </p><pre class="synopsis">

  193. substring(<em class="replaceable"><code>string</code></em>, <em class="replaceable"><code>pattern</code></em>, <em class="replaceable"><code>escape-character</code></em>)

  194. </pre><p>

  195.     As with <code class="literal">SIMILAR TO</code>, the

  196.     specified pattern must match the entire data string, or else the

  197.     function fails and returns null.  To indicate the part of the

  198.     pattern for which the matching data sub-string is of interest,

  199.     the pattern should contain

  200.     two occurrences of the escape character followed by a double quote

  201.     (<code class="literal">"</code>). 

  202.     The text matching the portion of the pattern

  203.     between these separators is returned when the match is successful.

  204.    </p><p>

  205.     The escape-double-quote separators actually

  206.     divide <code class="function">substring</code>'s pattern into three independent

  207.     regular expressions; for example, a vertical bar (<code class="literal">|</code>)

  208.     in any of the three sections affects only that section.  Also, the first

  209.     and third of these regular expressions are defined to match the smallest

  210.     possible amount of text, not the largest, when there is any ambiguity

  211.     about how much of the data string matches which pattern.  (In POSIX

  212.     parlance, the first and third regular expressions are forced to be

  213.     non-greedy.)

  214.    </p><p>

  215.     As an extension to the SQL standard, <span class="productname">PostgreSQL</span>

  216.     allows there to be just one escape-double-quote separator, in which case

  217.     the third regular expression is taken as empty; or no separators, in which

  218.     case the first and third regular expressions are taken as empty.

  219.    </p><p>

  220.     Some examples, with <code class="literal">#"</code> delimiting the return string:

  221. </p><pre class="programlisting">

  222. substring('foobar' from '%#"o_b#"%' for '#')   <em class="lineannotation"><span class="lineannotation">oob</span></em>

  223. substring('foobar' from '#"o_b#"%' for '#')    <em class="lineannotation"><span class="lineannotation">NULL</span></em>

  224. </pre><p>

  225.    </p></div><div class="sect2" id="FUNCTIONS-POSIX-REGEXP"><div class="titlepage"><div><div><h3 class="title">9.7.3. <acronym class="acronym">POSIX</acronym> Regular Expressions</h3></div></div></div><a id="id-1.5.8.12.9.2" class="indexterm"></a><a id="id-1.5.8.12.9.3" class="indexterm"></a><a id="id-1.5.8.12.9.4" class="indexterm"></a><a id="id-1.5.8.12.9.5" class="indexterm"></a><a id="id-1.5.8.12.9.6" class="indexterm"></a><a id="id-1.5.8.12.9.7" class="indexterm"></a><a id="id-1.5.8.12.9.8" class="indexterm"></a><p>

  226.     <a class="xref" href="functions-matching.html#FUNCTIONS-POSIX-TABLE" title="Table 9.15. Regular Expression Match Operators">Table 9.15</a> lists the available

  227.     operators for pattern matching using POSIX regular expressions.

  228.    </p><div class="table" id="FUNCTIONS-POSIX-TABLE"><p class="title"><strong>Table 9.15. Regular Expression Match Operators</strong></p><div class="table-contents"><table class="table" summary="Regular Expression Match Operators" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Operator</th><th>Description</th><th>Example</th></tr></thead><tbody><tr><td> <code class="literal">~</code> </td><td>Matches regular expression, case sensitive</td><td><code class="literal">'thomas' ~ '.*thomas.*'</code></td></tr><tr><td> <code class="literal">~*</code> </td><td>Matches regular expression, case insensitive</td><td><code class="literal">'thomas' ~* '.*Thomas.*'</code></td></tr><tr><td> <code class="literal">!~</code> </td><td>Does not match regular expression, case sensitive</td><td><code class="literal">'thomas' !~ '.*Thomas.*'</code></td></tr><tr><td> <code class="literal">!~*</code> </td><td>Does not match regular expression, case insensitive</td><td><code class="literal">'thomas' !~* '.*vadim.*'</code></td></tr></tbody></table></div></div><br class="table-break" /><p>

  229.      <acronym class="acronym">POSIX</acronym> regular expressions provide a more

  230.      powerful means for pattern matching than the <code class="function">LIKE</code> and

  231.      <code class="function">SIMILAR TO</code> operators.

  232.      Many Unix tools such as <code class="command">egrep</code>,

  233.      <code class="command">sed</code>, or <code class="command">awk</code> use a pattern

  234.      matching language that is similar to the one described here.

  235.     </p><p>

  236.      A regular expression is a character sequence that is an

  237.      abbreviated definition of a set of strings (a <em class="firstterm">regular

  238.      set</em>).  A string is said to match a regular expression

  239.      if it is a member of the regular set described by the regular

  240.      expression.  As with <code class="function">LIKE</code>, pattern characters

  241.      match string characters exactly unless they are special characters

  242.      in the regular expression language — but regular expressions use

  243.      different special characters than <code class="function">LIKE</code> does.

  244.      Unlike <code class="function">LIKE</code> patterns, a

  245.      regular expression is allowed to match anywhere within a string, unless

  246.      the regular expression is explicitly anchored to the beginning or

  247.      end of the string.

  248.     </p><p>

  249.      Some examples:

  250. </p><pre class="programlisting">

  251. 'abc' ~ 'abc'    <em class="lineannotation"><span class="lineannotation">true</span></em>

  252. 'abc' ~ '^a'     <em class="lineannotation"><span class="lineannotation">true</span></em>

  253. 'abc' ~ '(b|d)'  <em class="lineannotation"><span class="lineannotation">true</span></em>

  254. 'abc' ~ '^(b|c)' <em class="lineannotation"><span class="lineannotation">false</span></em>

  255. </pre><p>

  256.     </p><p>

  257.      The <acronym class="acronym">POSIX</acronym> pattern language is described in much

  258.      greater detail below.

  259.     </p><p>

  260.      The <code class="function">substring</code> function with two parameters,

  261.      <code class="function">substring(<em class="replaceable"><code>string</code></em> from

  262.      <em class="replaceable"><code>pattern</code></em>)</code>, provides extraction of a

  263.      substring

  264.      that matches a POSIX regular expression pattern.  It returns null if

  265.      there is no match, otherwise the portion of the text that matched the

  266.      pattern.  But if the pattern contains any parentheses, the portion

  267.      of the text that matched the first parenthesized subexpression (the

  268.      one whose left parenthesis comes first) is

  269.      returned.  You can put parentheses around the whole expression

  270.      if you want to use parentheses within it without triggering this

  271.      exception.  If you need parentheses in the pattern before the

  272.      subexpression you want to extract, see the non-capturing parentheses

  273.      described below.

  274.     </p><p>

  275.     Some examples:

  276. </p><pre class="programlisting">

  277. substring('foobar' from 'o.b')     <em class="lineannotation"><span class="lineannotation">oob</span></em>

  278. substring('foobar' from 'o(.)b')   <em class="lineannotation"><span class="lineannotation">o</span></em>

  279. </pre><p>

  280.    </p><p>

  281.      The <code class="function">regexp_replace</code> function provides substitution of

  282.      new text for substrings that match POSIX regular expression patterns.

  283.      It has the syntax

  284.      <code class="function">regexp_replace</code>(<em class="replaceable"><code>source</code></em>,

  285.      <em class="replaceable"><code>pattern</code></em>, <em class="replaceable"><code>replacement</code></em>

  286.      [<span class="optional">, <em class="replaceable"><code>flags</code></em> </span>]).

  287.      The <em class="replaceable"><code>source</code></em> string is returned unchanged if

  288.      there is no match to the <em class="replaceable"><code>pattern</code></em>.  If there is a

  289.      match, the <em class="replaceable"><code>source</code></em> string is returned with the

  290.      <em class="replaceable"><code>replacement</code></em> string substituted for the matching

  291.      substring.  The <em class="replaceable"><code>replacement</code></em> string can contain

  292.      <code class="literal">\</code><em class="replaceable"><code>n</code></em>, where <em class="replaceable"><code>n</code></em> is 1

  293.      through 9, to indicate that the source substring matching the

  294.      <em class="replaceable"><code>n</code></em>'th parenthesized subexpression of the pattern should be

  295.      inserted, and it can contain <code class="literal">\&amp;</code> to indicate that the

  296.      substring matching the entire pattern should be inserted.  Write

  297.      <code class="literal">\\</code> if you need to put a literal backslash in the replacement

  298.      text.

  299.      The <em class="replaceable"><code>flags</code></em> parameter is an optional text

  300.      string containing zero or more single-letter flags that change the

  301.      function's behavior.  Flag <code class="literal">i</code> specifies case-insensitive

  302.      matching, while flag <code class="literal">g</code> specifies replacement of each matching

  303.      substring rather than only the first one.  Supported flags (though

  304.      not <code class="literal">g</code>) are

  305.      described in <a class="xref" href="functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE" title="Table 9.23. ARE Embedded-Option Letters">Table 9.23</a>.

  306.     </p><p>

  307.     Some examples:

  308. </p><pre class="programlisting">

  309. regexp_replace('foobarbaz', 'b..', 'X')

  310.                                    <em class="lineannotation"><span class="lineannotation">fooXbaz</span></em>

  311. regexp_replace('foobarbaz', 'b..', 'X', 'g')

  312.                                    <em class="lineannotation"><span class="lineannotation">fooXX</span></em>

  313. regexp_replace('foobarbaz', 'b(..)', 'X\1Y', 'g')

  314.                                    <em class="lineannotation"><span class="lineannotation">fooXarYXazY</span></em>

  315. </pre><p>

  316.    </p><p>

  317.      The <code class="function">regexp_match</code> function returns a text array of

  318.      captured substring(s) resulting from the first match of a POSIX

  319.      regular expression pattern to a string.  It has the syntax

  320.      <code class="function">regexp_match</code>(<em class="replaceable"><code>string</code></em>,

  321.      <em class="replaceable"><code>pattern</code></em> [<span class="optional">, <em class="replaceable"><code>flags</code></em> </span>]).

  322.      If there is no match, the result is <code class="literal">NULL</code>.

  323.      If a match is found, and the <em class="replaceable"><code>pattern</code></em> contains no

  324.      parenthesized subexpressions, then the result is a single-element text

  325.      array containing the substring matching the whole pattern.

  326.      If a match is found, and the <em class="replaceable"><code>pattern</code></em> contains

  327.      parenthesized subexpressions, then the result is a text array

  328.      whose <em class="replaceable"><code>n</code></em>'th element is the substring matching

  329.      the <em class="replaceable"><code>n</code></em>'th parenthesized subexpression of

  330.      the <em class="replaceable"><code>pattern</code></em> (not counting <span class="quote">“<span class="quote">non-capturing</span>”</span>

  331.      parentheses; see below for details).

  332.      The <em class="replaceable"><code>flags</code></em> parameter is an optional text string

  333.      containing zero or more single-letter flags that change the function's

  334.      behavior.  Supported flags are described

  335.      in <a class="xref" href="functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE" title="Table 9.23. ARE Embedded-Option Letters">Table 9.23</a>.

  336.     </p><p>

  337.     Some examples:

  338. </p><pre class="programlisting">

  339. SELECT regexp_match('foobarbequebaz', 'bar.*que');

  340.  regexp_match

  341. --------------

  342.  {barbeque}

  343. (1 row)

  344. 

  345. SELECT regexp_match('foobarbequebaz', '(bar)(beque)');

  346.  regexp_match

  347. --------------

  348.  {bar,beque}

  349. (1 row)

  350. </pre><p>

  351.     In the common case where you just want the whole matching substring

  352.     or <code class="literal">NULL</code> for no match, write something like

  353. </p><pre class="programlisting">

  354. SELECT (regexp_match('foobarbequebaz', 'bar.*que'))[1];

  355.  regexp_match

  356. --------------

  357.  barbeque

  358. (1 row)

  359. </pre><p>

  360.    </p><p>

  361.      The <code class="function">regexp_matches</code> function returns a set of text arrays

  362.      of captured substring(s) resulting from matching a POSIX regular

  363.      expression pattern to a string.  It has the same syntax as

  364.      <code class="function">regexp_match</code>.

  365.      This function returns no rows if there is no match, one row if there is

  366.      a match and the <code class="literal">g</code> flag is not given, or <em class="replaceable"><code>N</code></em>

  367.      rows if there are <em class="replaceable"><code>N</code></em> matches and the <code class="literal">g</code> flag

  368.      is given.  Each returned row is a text array containing the whole

  369.      matched substring or the substrings matching parenthesized

  370.      subexpressions of the <em class="replaceable"><code>pattern</code></em>, just as described above

  371.      for <code class="function">regexp_match</code>.

  372.      <code class="function">regexp_matches</code> accepts all the flags shown

  373.      in <a class="xref" href="functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE" title="Table 9.23. ARE Embedded-Option Letters">Table 9.23</a>, plus

  374.      the <code class="literal">g</code> flag which commands it to return all matches, not

  375.      just the first one.

  376.     </p><p>

  377.     Some examples:

  378. </p><pre class="programlisting">

  379. SELECT regexp_matches('foo', 'not there');

  380.  regexp_matches

  381. ----------------

  382. (0 rows)

  383. 

  384. SELECT regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)', 'g');

  385.  regexp_matches

  386. ----------------

  387.  {bar,beque}

  388.  {bazil,barf}

  389. (2 rows)

  390. </pre><p>

  391.    </p><div class="tip"><h3 class="title">Tip</h3><p>

  392.      In most cases <code class="function">regexp_matches()</code> should be used with

  393.      the <code class="literal">g</code> flag, since if you only want the first match, it's

  394.      easier and more efficient to use <code class="function">regexp_match()</code>.

  395.      However, <code class="function">regexp_match()</code> only exists

  396.      in <span class="productname">PostgreSQL</span> version 10 and up.  When working in older

  397.      versions, a common trick is to place a <code class="function">regexp_matches()</code>

  398.      call in a sub-select, for example:

  399. </p><pre class="programlisting">

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

  401. </pre><p>

  402.      This produces a text array if there's a match, or <code class="literal">NULL</code> if

  403.      not, the same as <code class="function">regexp_match()</code> would do.  Without the

  404.      sub-select, this query would produce no output at all for table rows

  405.      without a match, which is typically not the desired behavior.

  406.     </p></div><p>

  407.      The <code class="function">regexp_split_to_table</code> function splits a string using a POSIX

  408.      regular expression pattern as a delimiter.  It has the syntax

  409.      <code class="function">regexp_split_to_table</code>(<em class="replaceable"><code>string</code></em>, <em class="replaceable"><code>pattern</code></em>

  410.      [<span class="optional">, <em class="replaceable"><code>flags</code></em> </span>]).

  411.      If there is no match to the <em class="replaceable"><code>pattern</code></em>, the function returns the

  412.      <em class="replaceable"><code>string</code></em>.  If there is at least one match, for each match it returns

  413.      the text from the end of the last match (or the beginning of the string)

  414.      to the beginning of the match.  When there are no more matches, it

  415.      returns the text from the end of the last match to the end of the string.

  416.      The <em class="replaceable"><code>flags</code></em> parameter is an optional text string containing

  417.      zero or more single-letter flags that change the function's behavior.

  418.      <code class="function">regexp_split_to_table</code> supports the flags described in

  419.      <a class="xref" href="functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE" title="Table 9.23. ARE Embedded-Option Letters">Table 9.23</a>.

  420.     </p><p>

  421.      The <code class="function">regexp_split_to_array</code> function behaves the same as

  422.      <code class="function">regexp_split_to_table</code>, except that <code class="function">regexp_split_to_array</code>

  423.      returns its result as an array of <code class="type">text</code>.  It has the syntax

  424.      <code class="function">regexp_split_to_array</code>(<em class="replaceable"><code>string</code></em>, <em class="replaceable"><code>pattern</code></em>

  425.      [<span class="optional">, <em class="replaceable"><code>flags</code></em> </span>]).

  426.      The parameters are the same as for <code class="function">regexp_split_to_table</code>.

  427.     </p><p>

  428.     Some examples:

  429. </p><pre class="programlisting">

  430. 

  431. SELECT foo FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', '\s+') AS foo;

  432.   foo   

  433. -------

  434.  the    

  435.  quick  

  436.  brown  

  437.  fox    

  438.  jumps 

  439.  over   

  440.  the    

  441.  lazy   

  442.  dog    

  443. (9 rows)

  444. 

  445. SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', '\s+');

  446.               regexp_split_to_array             

  447. -----------------------------------------------

  448.  {the,quick,brown,fox,jumps,over,the,lazy,dog}

  449. (1 row)

  450. 

  451. SELECT foo FROM regexp_split_to_table('the quick brown fox', '\s*') AS foo;

  452.  foo 

  453. -----

  454.  t         

  455.  h         

  456.  e         

  457.  q         

  458.  u         

  459.  i         

  460.  c         

  461.  k         

  462.  b         

  463.  r         

  464.  o         

  465.  w         

  466.  n         

  467.  f         

  468.  o         

  469.  x         

  470. (16 rows)

  471. </pre><p>

  472.    </p><p>

  473.     As the last example demonstrates, the regexp split functions ignore

  474.     zero-length matches that occur at the start or end of the string

  475.     or immediately after a previous match.  This is contrary to the strict

  476.     definition of regexp matching that is implemented by

  477.     <code class="function">regexp_match</code> and

  478.     <code class="function">regexp_matches</code>, but is usually the most convenient behavior

  479.     in practice.  Other software systems such as Perl use similar definitions.

  480.    </p><div class="sect3" id="POSIX-SYNTAX-DETAILS"><div class="titlepage"><div><div><h4 class="title">9.7.3.1. Regular Expression Details</h4></div></div></div><p>

  481.     <span class="productname">PostgreSQL</span>'s regular expressions are implemented

  482.     using a software package written by Henry Spencer.  Much of

  483.     the description of regular expressions below is copied verbatim from his

  484.     manual.

  485.    </p><p>

  486.     Regular expressions (<acronym class="acronym">RE</acronym>s), as defined in

  487.     <acronym class="acronym">POSIX</acronym> 1003.2, come in two forms:

  488.     <em class="firstterm">extended</em> <acronym class="acronym">RE</acronym>s or <acronym class="acronym">ERE</acronym>s

  489.     (roughly those of <code class="command">egrep</code>), and

  490.     <em class="firstterm">basic</em> <acronym class="acronym">RE</acronym>s or <acronym class="acronym">BRE</acronym>s

  491.     (roughly those of <code class="command">ed</code>).

  492.     <span class="productname">PostgreSQL</span> supports both forms, and

  493.     also implements some extensions

  494.     that are not in the POSIX standard, but have become widely used

  495.     due to their availability in programming languages such as Perl and Tcl.

  496.     <acronym class="acronym">RE</acronym>s using these non-POSIX extensions are called

  497.     <em class="firstterm">advanced</em> <acronym class="acronym">RE</acronym>s or <acronym class="acronym">ARE</acronym>s

  498.     in this documentation.  AREs are almost an exact superset of EREs,

  499.     but BREs have several notational incompatibilities (as well as being

  500.     much more limited).

  501.     We first describe the ARE and ERE forms, noting features that apply

  502.     only to AREs, and then describe how BREs differ.

  503.    </p><div class="note"><h3 class="title">Note</h3><p>

  504.      <span class="productname">PostgreSQL</span> always initially presumes that a regular

  505.      expression follows the ARE rules.  However, the more limited ERE or

  506.      BRE rules can be chosen by prepending an <em class="firstterm">embedded option</em>

  507.      to the RE pattern, as described in <a class="xref" href="functions-matching.html#POSIX-METASYNTAX" title="9.7.3.4. Regular Expression Metasyntax">Section 9.7.3.4</a>.

  508.      This can be useful for compatibility with applications that expect

  509.      exactly the <acronym class="acronym">POSIX</acronym> 1003.2 rules.

  510.     </p></div><p>

  511.     A regular expression is defined as one or more

  512.     <em class="firstterm">branches</em>, separated by

  513.     <code class="literal">|</code>.  It matches anything that matches one of the

  514.     branches.

  515.    </p><p>

  516.     A branch is zero or more <em class="firstterm">quantified atoms</em> or

  517.     <em class="firstterm">constraints</em>, concatenated.

  518.     It matches a match for the first, followed by a match for the second, etc;

  519.     an empty branch matches the empty string.

  520.    </p><p>

  521.     A quantified atom is an <em class="firstterm">atom</em> possibly followed

  522.     by a single <em class="firstterm">quantifier</em>.

  523.     Without a quantifier, it matches a match for the atom.

  524.     With a quantifier, it can match some number of matches of the atom.

  525.     An <em class="firstterm">atom</em> can be any of the possibilities

  526.     shown in <a class="xref" href="functions-matching.html#POSIX-ATOMS-TABLE" title="Table 9.16. Regular Expression Atoms">Table 9.16</a>.

  527.     The possible quantifiers and their meanings are shown in

  528.     <a class="xref" href="functions-matching.html#POSIX-QUANTIFIERS-TABLE" title="Table 9.17. Regular Expression Quantifiers">Table 9.17</a>.

  529.    </p><p>

  530.     A <em class="firstterm">constraint</em> matches an empty string, but matches only when

  531.     specific conditions are met.  A constraint can be used where an atom

  532.     could be used, except it cannot be followed by a quantifier.

  533.     The simple constraints are shown in

  534.     <a class="xref" href="functions-matching.html#POSIX-CONSTRAINTS-TABLE" title="Table 9.18. Regular Expression Constraints">Table 9.18</a>;

  535.     some more constraints are described later.

  536.    </p><div class="table" id="POSIX-ATOMS-TABLE"><p class="title"><strong>Table 9.16. Regular Expression Atoms</strong></p><div class="table-contents"><table class="table" summary="Regular Expression Atoms" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Atom</th><th>Description</th></tr></thead><tbody><tr><td> <code class="literal">(</code><em class="replaceable"><code>re</code></em><code class="literal">)</code> </td><td> (where <em class="replaceable"><code>re</code></em> is any regular expression)

  537.        matches a match for

  538.        <em class="replaceable"><code>re</code></em>, with the match noted for possible reporting </td></tr><tr><td> <code class="literal">(?:</code><em class="replaceable"><code>re</code></em><code class="literal">)</code> </td><td> as above, but the match is not noted for reporting

  539.        (a <span class="quote">“<span class="quote">non-capturing</span>”</span> set of parentheses)

  540.        (AREs only) </td></tr><tr><td> <code class="literal">.</code> </td><td> matches any single character </td></tr><tr><td> <code class="literal">[</code><em class="replaceable"><code>chars</code></em><code class="literal">]</code> </td><td> a <em class="firstterm">bracket expression</em>,

  541.        matching any one of the <em class="replaceable"><code>chars</code></em> (see

  542.        <a class="xref" href="functions-matching.html#POSIX-BRACKET-EXPRESSIONS" title="9.7.3.2. Bracket Expressions">Section 9.7.3.2</a> for more detail) </td></tr><tr><td> <code class="literal">\</code><em class="replaceable"><code>k</code></em> </td><td> (where <em class="replaceable"><code>k</code></em> is a non-alphanumeric character)

  543.        matches that character taken as an ordinary character,

  544.        e.g., <code class="literal">\\</code> matches a backslash character </td></tr><tr><td> <code class="literal">\</code><em class="replaceable"><code>c</code></em> </td><td> where <em class="replaceable"><code>c</code></em> is alphanumeric

  545.        (possibly followed by other characters)

  546.        is an <em class="firstterm">escape</em>, see <a class="xref" href="functions-matching.html#POSIX-ESCAPE-SEQUENCES" title="9.7.3.3. Regular Expression Escapes">Section 9.7.3.3</a>

  547.        (AREs only; in EREs and BREs, this matches <em class="replaceable"><code>c</code></em>) </td></tr><tr><td> <code class="literal">{</code> </td><td> when followed by a character other than a digit,

  548.        matches the left-brace character <code class="literal">{</code>;

  549.        when followed by a digit, it is the beginning of a

  550.        <em class="replaceable"><code>bound</code></em> (see below) </td></tr><tr><td> <em class="replaceable"><code>x</code></em> </td><td> where <em class="replaceable"><code>x</code></em> is a single character with no other

  551.        significance, matches that character </td></tr></tbody></table></div></div><br class="table-break" /><p>

  552.     An RE cannot end with a backslash (<code class="literal">\</code>).

  553.    </p><div class="note"><h3 class="title">Note</h3><p>

  554.      If you have <a class="xref" href="runtime-config-compatible.html#GUC-STANDARD-CONFORMING-STRINGS">standard_conforming_strings</a> turned off,

  555.      any backslashes you write in literal string constants will need to be

  556.      doubled.  See <a class="xref" href="sql-syntax-lexical.html#SQL-SYNTAX-STRINGS" title="4.1.2.1. String Constants">Section 4.1.2.1</a> for more information.

  557.     </p></div><div class="table" id="POSIX-QUANTIFIERS-TABLE"><p class="title"><strong>Table 9.17. Regular Expression Quantifiers</strong></p><div class="table-contents"><table class="table" summary="Regular Expression Quantifiers" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Quantifier</th><th>Matches</th></tr></thead><tbody><tr><td> <code class="literal">*</code> </td><td> a sequence of 0 or more matches of the atom </td></tr><tr><td> <code class="literal">+</code> </td><td> a sequence of 1 or more matches of the atom </td></tr><tr><td> <code class="literal">?</code> </td><td> a sequence of 0 or 1 matches of the atom </td></tr><tr><td> <code class="literal">{</code><em class="replaceable"><code>m</code></em><code class="literal">}</code> </td><td> a sequence of exactly <em class="replaceable"><code>m</code></em> matches of the atom </td></tr><tr><td> <code class="literal">{</code><em class="replaceable"><code>m</code></em><code class="literal">,}</code> </td><td> a sequence of <em class="replaceable"><code>m</code></em> or more matches of the atom </td></tr><tr><td>

  558.        <code class="literal">{</code><em class="replaceable"><code>m</code></em><code class="literal">,</code><em class="replaceable"><code>n</code></em><code class="literal">}</code> </td><td> a sequence of <em class="replaceable"><code>m</code></em> through <em class="replaceable"><code>n</code></em>

  559.        (inclusive) matches of the atom; <em class="replaceable"><code>m</code></em> cannot exceed

  560.        <em class="replaceable"><code>n</code></em> </td></tr><tr><td> <code class="literal">*?</code> </td><td> non-greedy version of <code class="literal">*</code> </td></tr><tr><td> <code class="literal">+?</code> </td><td> non-greedy version of <code class="literal">+</code> </td></tr><tr><td> <code class="literal">??</code> </td><td> non-greedy version of <code class="literal">?</code> </td></tr><tr><td> <code class="literal">{</code><em class="replaceable"><code>m</code></em><code class="literal">}?</code> </td><td> non-greedy version of <code class="literal">{</code><em class="replaceable"><code>m</code></em><code class="literal">}</code> </td></tr><tr><td> <code class="literal">{</code><em class="replaceable"><code>m</code></em><code class="literal">,}?</code> </td><td> non-greedy version of <code class="literal">{</code><em class="replaceable"><code>m</code></em><code class="literal">,}</code> </td></tr><tr><td>

  561.        <code class="literal">{</code><em class="replaceable"><code>m</code></em><code class="literal">,</code><em class="replaceable"><code>n</code></em><code class="literal">}?</code> </td><td> non-greedy version of <code class="literal">{</code><em class="replaceable"><code>m</code></em><code class="literal">,</code><em class="replaceable"><code>n</code></em><code class="literal">}</code> </td></tr></tbody></table></div></div><br class="table-break" /><p>

  562.     The forms using <code class="literal">{</code><em class="replaceable"><code>...</code></em><code class="literal">}</code>

  563.     are known as <em class="firstterm">bounds</em>.

  564.     The numbers <em class="replaceable"><code>m</code></em> and <em class="replaceable"><code>n</code></em> within a bound are

  565.     unsigned decimal integers with permissible values from 0 to 255 inclusive.

  566.    </p><p>

  567.      <em class="firstterm">Non-greedy</em> quantifiers (available in AREs only) match the

  568.      same possibilities as their corresponding normal (<em class="firstterm">greedy</em>)

  569.      counterparts, but prefer the smallest number rather than the largest

  570.      number of matches.

  571.      See <a class="xref" href="functions-matching.html#POSIX-MATCHING-RULES" title="9.7.3.5. Regular Expression Matching Rules">Section 9.7.3.5</a> for more detail.

  572.    </p><div class="note"><h3 class="title">Note</h3><p>

  573.      A quantifier cannot immediately follow another quantifier, e.g.,

  574.      <code class="literal">**</code> is invalid.

  575.      A quantifier cannot

  576.      begin an expression or subexpression or follow

  577.      <code class="literal">^</code> or <code class="literal">|</code>.

  578.     </p></div><div class="table" id="POSIX-CONSTRAINTS-TABLE"><p class="title"><strong>Table 9.18. Regular Expression Constraints</strong></p><div class="table-contents"><table class="table" summary="Regular Expression Constraints" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Constraint</th><th>Description</th></tr></thead><tbody><tr><td> <code class="literal">^</code> </td><td> matches at the beginning of the string </td></tr><tr><td> <code class="literal">$</code> </td><td> matches at the end of the string </td></tr><tr><td> <code class="literal">(?=</code><em class="replaceable"><code>re</code></em><code class="literal">)</code> </td><td> <em class="firstterm">positive lookahead</em> matches at any point

  579.        where a substring matching <em class="replaceable"><code>re</code></em> begins

  580.        (AREs only) </td></tr><tr><td> <code class="literal">(?!</code><em class="replaceable"><code>re</code></em><code class="literal">)</code> </td><td> <em class="firstterm">negative lookahead</em> matches at any point

  581.        where no substring matching <em class="replaceable"><code>re</code></em> begins

  582.        (AREs only) </td></tr><tr><td> <code class="literal">(?&lt;=</code><em class="replaceable"><code>re</code></em><code class="literal">)</code> </td><td> <em class="firstterm">positive lookbehind</em> matches at any point

  583.        where a substring matching <em class="replaceable"><code>re</code></em> ends

  584.        (AREs only) </td></tr><tr><td> <code class="literal">(?&lt;!</code><em class="replaceable"><code>re</code></em><code class="literal">)</code> </td><td> <em class="firstterm">negative lookbehind</em> matches at any point

  585.        where no substring matching <em class="replaceable"><code>re</code></em> ends

  586.        (AREs only) </td></tr></tbody></table></div></div><br class="table-break" /><p>

  587.     Lookahead and lookbehind constraints cannot contain <em class="firstterm">back

  588.     references</em> (see <a class="xref" href="functions-matching.html#POSIX-ESCAPE-SEQUENCES" title="9.7.3.3. Regular Expression Escapes">Section 9.7.3.3</a>),

  589.     and all parentheses within them are considered non-capturing.

  590.    </p></div><div class="sect3" id="POSIX-BRACKET-EXPRESSIONS"><div class="titlepage"><div><div><h4 class="title">9.7.3.2. Bracket Expressions</h4></div></div></div><p>

  591.     A <em class="firstterm">bracket expression</em> is a list of

  592.     characters enclosed in <code class="literal">[]</code>.  It normally matches

  593.     any single character from the list (but see below).  If the list

  594.     begins with <code class="literal">^</code>, it matches any single character

  595.     <span class="emphasis"><em>not</em></span> from the rest of the list.

  596.     If two characters

  597.     in the list are separated by <code class="literal">-</code>, this is

  598.     shorthand for the full range of characters between those two

  599.     (inclusive) in the collating sequence,

  600.     e.g., <code class="literal">[0-9]</code> in <acronym class="acronym">ASCII</acronym> matches

  601.     any decimal digit.  It is illegal for two ranges to share an

  602.     endpoint, e.g.,  <code class="literal">a-c-e</code>.  Ranges are very

  603.     collating-sequence-dependent, so portable programs should avoid

  604.     relying on them.

  605.    </p><p>

  606.     To include a literal <code class="literal">]</code> in the list, make it the

  607.     first character (after <code class="literal">^</code>, if that is used).  To

  608.     include a literal <code class="literal">-</code>, make it the first or last

  609.     character, or the second endpoint of a range.  To use a literal

  610.     <code class="literal">-</code> as the first endpoint of a range, enclose it

  611.     in <code class="literal">[.</code> and <code class="literal">.]</code> to make it a

  612.     collating element (see below).  With the exception of these characters,

  613.     some combinations using <code class="literal">[</code>

  614.     (see next paragraphs), and escapes (AREs only), all other special

  615.     characters lose their special significance within a bracket expression.

  616.     In particular, <code class="literal">\</code> is not special when following

  617.     ERE or BRE rules, though it is special (as introducing an escape)

  618.     in AREs.

  619.    </p><p>

  620.     Within a bracket expression, a collating element (a character, a

  621.     multiple-character sequence that collates as if it were a single

  622.     character, or a collating-sequence name for either) enclosed in

  623.     <code class="literal">[.</code> and <code class="literal">.]</code> stands for the

  624.     sequence of characters of that collating element.  The sequence is

  625.     treated as a single element of the bracket expression's list.  This

  626.     allows a bracket

  627.     expression containing a multiple-character collating element to

  628.     match more than one character, e.g., if the collating sequence

  629.     includes a <code class="literal">ch</code> collating element, then the RE

  630.     <code class="literal">[[.ch.]]*c</code> matches the first five characters of

  631.     <code class="literal">chchcc</code>.

  632.    </p><div class="note"><h3 class="title">Note</h3><p>

  633.      <span class="productname">PostgreSQL</span> currently does not support multi-character collating

  634.      elements. This information describes possible future behavior.

  635.     </p></div><p>

  636.     Within a bracket expression, a collating element enclosed in

  637.     <code class="literal">[=</code> and <code class="literal">=]</code> is an <em class="firstterm">equivalence

  638.     class</em>, standing for the sequences of characters of all collating

  639.     elements equivalent to that one, including itself.  (If there are

  640.     no other equivalent collating elements, the treatment is as if the

  641.     enclosing delimiters were <code class="literal">[.</code> and

  642.     <code class="literal">.]</code>.)  For example, if <code class="literal">o</code> and

  643.     <code class="literal">^</code> are the members of an equivalence class, then

  644.     <code class="literal">[[=o=]]</code>, <code class="literal">[[=^=]]</code>, and

  645.     <code class="literal">[o^]</code> are all synonymous.  An equivalence class

  646.     cannot be an endpoint of a range.

  647.    </p><p>

  648.     Within a bracket expression, the name of a character class

  649.     enclosed in <code class="literal">[:</code> and <code class="literal">:]</code> stands

  650.     for the list of all characters belonging to that class.  A character

  651.     class cannot be used as an endpoint of a range.

  652.     The <acronym class="acronym">POSIX</acronym> standard defines these character class

  653.     names:

  654.     <code class="literal">alnum</code> (letters and numeric digits),

  655.     <code class="literal">alpha</code> (letters),

  656.     <code class="literal">blank</code> (space and tab),

  657.     <code class="literal">cntrl</code> (control characters),

  658.     <code class="literal">digit</code> (numeric digits),

  659.     <code class="literal">graph</code> (printable characters except space),

  660.     <code class="literal">lower</code> (lower-case letters),

  661.     <code class="literal">print</code> (printable characters including space),

  662.     <code class="literal">punct</code> (punctuation),

  663.     <code class="literal">space</code> (any white space),

  664.     <code class="literal">upper</code> (upper-case letters),

  665.     and <code class="literal">xdigit</code> (hexadecimal digits).

  666.     The behavior of these standard character classes is generally

  667.     consistent across platforms for characters in the 7-bit ASCII set.

  668.     Whether a given non-ASCII character is considered to belong to one

  669.     of these classes depends on the <em class="firstterm">collation</em>

  670.     that is used for the regular-expression function or operator

  671.     (see <a class="xref" href="collation.html" title="23.2. Collation Support">Section 23.2</a>), or by default on the

  672.     database's <code class="envar">LC_CTYPE</code> locale setting (see

  673.     <a class="xref" href="locale.html" title="23.1. Locale Support">Section 23.1</a>).  The classification of non-ASCII

  674.     characters can vary across platforms even in similarly-named

  675.     locales.  (But the <code class="literal">C</code> locale never considers any

  676.     non-ASCII characters to belong to any of these classes.)

  677.     In addition to these standard character

  678.     classes, <span class="productname">PostgreSQL</span> defines

  679.     the <code class="literal">ascii</code> character class, which contains exactly

  680.     the 7-bit ASCII set.

  681.    </p><p>

  682.     There are two special cases of bracket expressions:  the bracket

  683.     expressions <code class="literal">[[:&lt;:]]</code> and

  684.     <code class="literal">[[:&gt;:]]</code> are constraints,

  685.     matching empty strings at the beginning

  686.     and end of a word respectively.  A word is defined as a sequence

  687.     of word characters that is neither preceded nor followed by word

  688.     characters.  A word character is an <code class="literal">alnum</code> character (as

  689.     defined by the <acronym class="acronym">POSIX</acronym> character class described above)

  690.     or an underscore.  This is an extension, compatible with but not

  691.     specified by <acronym class="acronym">POSIX</acronym> 1003.2, and should be used with

  692.     caution in software intended to be portable to other systems.

  693.     The constraint escapes described below are usually preferable; they

  694.     are no more standard, but are easier to type.

  695.    </p></div><div class="sect3" id="POSIX-ESCAPE-SEQUENCES"><div class="titlepage"><div><div><h4 class="title">9.7.3.3. Regular Expression Escapes</h4></div></div></div><p>

  696.     <em class="firstterm">Escapes</em> are special sequences beginning with <code class="literal">\</code>

  697.     followed by an alphanumeric character. Escapes come in several varieties:

  698.     character entry, class shorthands, constraint escapes, and back references.

  699.     A <code class="literal">\</code> followed by an alphanumeric character but not constituting

  700.     a valid escape is illegal in AREs.

  701.     In EREs, there are no escapes: outside a bracket expression,

  702.     a <code class="literal">\</code> followed by an alphanumeric character merely stands for

  703.     that character as an ordinary character, and inside a bracket expression,

  704.     <code class="literal">\</code> is an ordinary character.

  705.     (The latter is the one actual incompatibility between EREs and AREs.)

  706.    </p><p>

  707.     <em class="firstterm">Character-entry escapes</em> exist to make it easier to specify

  708.     non-printing and other inconvenient characters in REs.  They are

  709.     shown in <a class="xref" href="functions-matching.html#POSIX-CHARACTER-ENTRY-ESCAPES-TABLE" title="Table 9.19. Regular Expression Character-Entry Escapes">Table 9.19</a>.

  710.    </p><p>

  711.     <em class="firstterm">Class-shorthand escapes</em> provide shorthands for certain

  712.     commonly-used character classes.  They are

  713.     shown in <a class="xref" href="functions-matching.html#POSIX-CLASS-SHORTHAND-ESCAPES-TABLE" title="Table 9.20. Regular Expression Class-Shorthand Escapes">Table 9.20</a>.

  714.    </p><p>

  715.     A <em class="firstterm">constraint escape</em> is a constraint,

  716.     matching the empty string if specific conditions are met,

  717.     written as an escape.  They are

  718.     shown in <a class="xref" href="functions-matching.html#POSIX-CONSTRAINT-ESCAPES-TABLE" title="Table 9.21. Regular Expression Constraint Escapes">Table 9.21</a>.

  719.    </p><p>

  720.     A <em class="firstterm">back reference</em> (<code class="literal">\</code><em class="replaceable"><code>n</code></em>) matches the

  721.     same string matched by the previous parenthesized subexpression specified

  722.     by the number <em class="replaceable"><code>n</code></em>

  723.     (see <a class="xref" href="functions-matching.html#POSIX-CONSTRAINT-BACKREF-TABLE" title="Table 9.22. Regular Expression Back References">Table 9.22</a>).  For example,

  724.     <code class="literal">([bc])\1</code> matches <code class="literal">bb</code> or <code class="literal">cc</code>

  725.     but not <code class="literal">bc</code> or <code class="literal">cb</code>.

  726.     The subexpression must entirely precede the back reference in the RE.

  727.     Subexpressions are numbered in the order of their leading parentheses.

  728.     Non-capturing parentheses do not define subexpressions.

  729.    </p><div class="table" id="POSIX-CHARACTER-ENTRY-ESCAPES-TABLE"><p class="title"><strong>Table 9.19. Regular Expression Character-Entry Escapes</strong></p><div class="table-contents"><table class="table" summary="Regular Expression Character-Entry Escapes" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Escape</th><th>Description</th></tr></thead><tbody><tr><td> <code class="literal">\a</code> </td><td> alert (bell) character, as in C </td></tr><tr><td> <code class="literal">\b</code> </td><td> backspace, as in C </td></tr><tr><td> <code class="literal">\B</code> </td><td> synonym for backslash (<code class="literal">\</code>) to help reduce the need for backslash

  730.        doubling </td></tr><tr><td> <code class="literal">\c</code><em class="replaceable"><code>X</code></em> </td><td> (where <em class="replaceable"><code>X</code></em> is any character) the character whose

  731.        low-order 5 bits are the same as those of

  732.        <em class="replaceable"><code>X</code></em>, and whose other bits are all zero </td></tr><tr><td> <code class="literal">\e</code> </td><td> the character whose collating-sequence name

  733.        is <code class="literal">ESC</code>,

  734.        or failing that, the character with octal value <code class="literal">033</code> </td></tr><tr><td> <code class="literal">\f</code> </td><td> form feed, as in C </td></tr><tr><td> <code class="literal">\n</code> </td><td> newline, as in C </td></tr><tr><td> <code class="literal">\r</code> </td><td> carriage return, as in C </td></tr><tr><td> <code class="literal">\t</code> </td><td> horizontal tab, as in C </td></tr><tr><td> <code class="literal">\u</code><em class="replaceable"><code>wxyz</code></em> </td><td> (where <em class="replaceable"><code>wxyz</code></em> is exactly four hexadecimal digits)

  735.        the character whose hexadecimal value is

  736.        <code class="literal">0x</code><em class="replaceable"><code>wxyz</code></em>

  737.        </td></tr><tr><td> <code class="literal">\U</code><em class="replaceable"><code>stuvwxyz</code></em> </td><td> (where <em class="replaceable"><code>stuvwxyz</code></em> is exactly eight hexadecimal

  738.        digits)

  739.        the character whose hexadecimal value is

  740.        <code class="literal">0x</code><em class="replaceable"><code>stuvwxyz</code></em>

  741.        </td></tr><tr><td> <code class="literal">\v</code> </td><td> vertical tab, as in C </td></tr><tr><td> <code class="literal">\x</code><em class="replaceable"><code>hhh</code></em> </td><td> (where <em class="replaceable"><code>hhh</code></em> is any sequence of hexadecimal

  742.        digits)

  743.        the character whose hexadecimal value is

  744.        <code class="literal">0x</code><em class="replaceable"><code>hhh</code></em>

  745.        (a single character no matter how many hexadecimal digits are used)

  746.        </td></tr><tr><td> <code class="literal">\0</code> </td><td> the character whose value is <code class="literal">0</code> (the null byte)</td></tr><tr><td> <code class="literal">\</code><em class="replaceable"><code>xy</code></em> </td><td> (where <em class="replaceable"><code>xy</code></em> is exactly two octal digits,

  747.        and is not a <em class="firstterm">back reference</em>)

  748.        the character whose octal value is

  749.        <code class="literal">0</code><em class="replaceable"><code>xy</code></em> </td></tr><tr><td> <code class="literal">\</code><em class="replaceable"><code>xyz</code></em> </td><td> (where <em class="replaceable"><code>xyz</code></em> is exactly three octal digits,

  750.        and is not a <em class="firstterm">back reference</em>)

  751.        the character whose octal value is

  752.        <code class="literal">0</code><em class="replaceable"><code>xyz</code></em> </td></tr></tbody></table></div></div><br class="table-break" /><p>

  753.     Hexadecimal digits are <code class="literal">0</code>-<code class="literal">9</code>,

  754.     <code class="literal">a</code>-<code class="literal">f</code>, and <code class="literal">A</code>-<code class="literal">F</code>.

  755.     Octal digits are <code class="literal">0</code>-<code class="literal">7</code>.

  756.    </p><p>

  757.     Numeric character-entry escapes specifying values outside the ASCII range

  758.     (0-127) have meanings dependent on the database encoding.  When the

  759.     encoding is UTF-8, escape values are equivalent to Unicode code points,

  760.     for example <code class="literal">\u1234</code> means the character <code class="literal">U+1234</code>.

  761.     For other multibyte encodings, character-entry escapes usually just

  762.     specify the concatenation of the byte values for the character.  If the

  763.     escape value does not correspond to any legal character in the database

  764.     encoding, no error will be raised, but it will never match any data.

  765.    </p><p>

  766.     The character-entry escapes are always taken as ordinary characters.

  767.     For example, <code class="literal">\135</code> is <code class="literal">]</code> in ASCII, but

  768.     <code class="literal">\135</code> does not terminate a bracket expression.

  769.    </p><div class="table" id="POSIX-CLASS-SHORTHAND-ESCAPES-TABLE"><p class="title"><strong>Table 9.20. Regular Expression Class-Shorthand Escapes</strong></p><div class="table-contents"><table class="table" summary="Regular Expression Class-Shorthand Escapes" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Escape</th><th>Description</th></tr></thead><tbody><tr><td> <code class="literal">\d</code> </td><td> <code class="literal">[[:digit:]]</code> </td></tr><tr><td> <code class="literal">\s</code> </td><td> <code class="literal">[[:space:]]</code> </td></tr><tr><td> <code class="literal">\w</code> </td><td> <code class="literal">[[:alnum:]_]</code>

  770.        (note underscore is included) </td></tr><tr><td> <code class="literal">\D</code> </td><td> <code class="literal">[^[:digit:]]</code> </td></tr><tr><td> <code class="literal">\S</code> </td><td> <code class="literal">[^[:space:]]</code> </td></tr><tr><td> <code class="literal">\W</code> </td><td> <code class="literal">[^[:alnum:]_]</code>

  771.        (note underscore is included) </td></tr></tbody></table></div></div><br class="table-break" /><p>

  772.     Within bracket expressions, <code class="literal">\d</code>, <code class="literal">\s</code>,

  773.     and <code class="literal">\w</code> lose their outer brackets,

  774.     and <code class="literal">\D</code>, <code class="literal">\S</code>, and <code class="literal">\W</code> are illegal.

  775.     (So, for example, <code class="literal">[a-c\d]</code> is equivalent to

  776.     <code class="literal">[a-c[:digit:]]</code>.

  777.     Also, <code class="literal">[a-c\D]</code>, which is equivalent to

  778.     <code class="literal">[a-c^[:digit:]]</code>, is illegal.)

  779.    </p><div class="table" id="POSIX-CONSTRAINT-ESCAPES-TABLE"><p class="title"><strong>Table 9.21. Regular Expression Constraint Escapes</strong></p><div class="table-contents"><table class="table" summary="Regular Expression Constraint Escapes" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Escape</th><th>Description</th></tr></thead><tbody><tr><td> <code class="literal">\A</code> </td><td> matches only at the beginning of the string

  780.        (see <a class="xref" href="functions-matching.html#POSIX-MATCHING-RULES" title="9.7.3.5. Regular Expression Matching Rules">Section 9.7.3.5</a> for how this differs from

  781.        <code class="literal">^</code>) </td></tr><tr><td> <code class="literal">\m</code> </td><td> matches only at the beginning of a word </td></tr><tr><td> <code class="literal">\M</code> </td><td> matches only at the end of a word </td></tr><tr><td> <code class="literal">\y</code> </td><td> matches only at the beginning or end of a word </td></tr><tr><td> <code class="literal">\Y</code> </td><td> matches only at a point that is not the beginning or end of a

  782.        word </td></tr><tr><td> <code class="literal">\Z</code> </td><td> matches only at the end of the string

  783.        (see <a class="xref" href="functions-matching.html#POSIX-MATCHING-RULES" title="9.7.3.5. Regular Expression Matching Rules">Section 9.7.3.5</a> for how this differs from

  784.        <code class="literal">$</code>) </td></tr></tbody></table></div></div><br class="table-break" /><p>

  785.     A word is defined as in the specification of

  786.     <code class="literal">[[:&lt;:]]</code> and <code class="literal">[[:&gt;:]]</code> above.

  787.     Constraint escapes are illegal within bracket expressions.

  788.    </p><div class="table" id="POSIX-CONSTRAINT-BACKREF-TABLE"><p class="title"><strong>Table 9.22. Regular Expression Back References</strong></p><div class="table-contents"><table class="table" summary="Regular Expression Back References" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Escape</th><th>Description</th></tr></thead><tbody><tr><td> <code class="literal">\</code><em class="replaceable"><code>m</code></em> </td><td> (where <em class="replaceable"><code>m</code></em> is a nonzero digit)

  789.        a back reference to the <em class="replaceable"><code>m</code></em>'th subexpression </td></tr><tr><td> <code class="literal">\</code><em class="replaceable"><code>mnn</code></em> </td><td> (where <em class="replaceable"><code>m</code></em> is a nonzero digit, and

  790.        <em class="replaceable"><code>nn</code></em> is some more digits, and the decimal value

  791.        <em class="replaceable"><code>mnn</code></em> is not greater than the number of closing capturing

  792.        parentheses seen so far)

  793.        a back reference to the <em class="replaceable"><code>mnn</code></em>'th subexpression </td></tr></tbody></table></div></div><br class="table-break" /><div class="note"><h3 class="title">Note</h3><p>

  794.      There is an inherent ambiguity between octal character-entry

  795.      escapes and back references, which is resolved by the following heuristics,

  796.      as hinted at above.

  797.      A leading zero always indicates an octal escape.

  798.      A single non-zero digit, not followed by another digit,

  799.      is always taken as a back reference.

  800.      A multi-digit sequence not starting with a zero is taken as a back

  801.      reference if it comes after a suitable subexpression

  802.      (i.e., the number is in the legal range for a back reference),

  803.      and otherwise is taken as octal.

  804.     </p></div></div><div class="sect3" id="POSIX-METASYNTAX"><div class="titlepage"><div><div><h4 class="title">9.7.3.4. Regular Expression Metasyntax</h4></div></div></div><p>

  805.     In addition to the main syntax described above, there are some special

  806.     forms and miscellaneous syntactic facilities available.

  807.    </p><p>

  808.     An RE can begin with one of two special <em class="firstterm">director</em> prefixes.

  809.     If an RE begins with <code class="literal">***:</code>,

  810.     the rest of the RE is taken as an ARE.  (This normally has no effect in

  811.     <span class="productname">PostgreSQL</span>, since REs are assumed to be AREs;

  812.     but it does have an effect if ERE or BRE mode had been specified by

  813.     the <em class="replaceable"><code>flags</code></em> parameter to a regex function.)

  814.     If an RE begins with <code class="literal">***=</code>,

  815.     the rest of the RE is taken to be a literal string,

  816.     with all characters considered ordinary characters.

  817.    </p><p>

  818.     An ARE can begin with <em class="firstterm">embedded options</em>:

  819.     a sequence <code class="literal">(?</code><em class="replaceable"><code>xyz</code></em><code class="literal">)</code>

  820.     (where <em class="replaceable"><code>xyz</code></em> is one or more alphabetic characters)

  821.     specifies options affecting the rest of the RE.

  822.     These options override any previously determined options —

  823.     in particular, they can override the case-sensitivity behavior implied by

  824.     a regex operator, or the <em class="replaceable"><code>flags</code></em> parameter to a regex

  825.     function.

  826.     The available option letters are

  827.     shown in <a class="xref" href="functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE" title="Table 9.23. ARE Embedded-Option Letters">Table 9.23</a>.

  828.     Note that these same option letters are used in the <em class="replaceable"><code>flags</code></em>

  829.     parameters of regex functions.

  830.    </p><div class="table" id="POSIX-EMBEDDED-OPTIONS-TABLE"><p class="title"><strong>Table 9.23. ARE Embedded-Option Letters</strong></p><div class="table-contents"><table class="table" summary="ARE Embedded-Option Letters" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Option</th><th>Description</th></tr></thead><tbody><tr><td> <code class="literal">b</code> </td><td> rest of RE is a BRE </td></tr><tr><td> <code class="literal">c</code> </td><td> case-sensitive matching (overrides operator type) </td></tr><tr><td> <code class="literal">e</code> </td><td> rest of RE is an ERE </td></tr><tr><td> <code class="literal">i</code> </td><td> case-insensitive matching (see

  831.        <a class="xref" href="functions-matching.html#POSIX-MATCHING-RULES" title="9.7.3.5. Regular Expression Matching Rules">Section 9.7.3.5</a>) (overrides operator type) </td></tr><tr><td> <code class="literal">m</code> </td><td> historical synonym for <code class="literal">n</code> </td></tr><tr><td> <code class="literal">n</code> </td><td> newline-sensitive matching (see

  832.        <a class="xref" href="functions-matching.html#POSIX-MATCHING-RULES" title="9.7.3.5. Regular Expression Matching Rules">Section 9.7.3.5</a>) </td></tr><tr><td> <code class="literal">p</code> </td><td> partial newline-sensitive matching (see

  833.        <a class="xref" href="functions-matching.html#POSIX-MATCHING-RULES" title="9.7.3.5. Regular Expression Matching Rules">Section 9.7.3.5</a>) </td></tr><tr><td> <code class="literal">q</code> </td><td> rest of RE is a literal (<span class="quote">“<span class="quote">quoted</span>”</span>) string, all ordinary

  834.        characters </td></tr><tr><td> <code class="literal">s</code> </td><td> non-newline-sensitive matching (default) </td></tr><tr><td> <code class="literal">t</code> </td><td> tight syntax (default; see below) </td></tr><tr><td> <code class="literal">w</code> </td><td> inverse partial newline-sensitive (<span class="quote">“<span class="quote">weird</span>”</span>) matching

  835.        (see <a class="xref" href="functions-matching.html#POSIX-MATCHING-RULES" title="9.7.3.5. Regular Expression Matching Rules">Section 9.7.3.5</a>) </td></tr><tr><td> <code class="literal">x</code> </td><td> expanded syntax (see below) </td></tr></tbody></table></div></div><br class="table-break" /><p>

  836.     Embedded options take effect at the <code class="literal">)</code> terminating the sequence.

  837.     They can appear only at the start of an ARE (after the

  838.     <code class="literal">***:</code> director if any).

  839.    </p><p>

  840.     In addition to the usual (<em class="firstterm">tight</em>) RE syntax, in which all

  841.     characters are significant, there is an <em class="firstterm">expanded</em> syntax,

  842.     available by specifying the embedded <code class="literal">x</code> option.

  843.     In the expanded syntax,

  844.     white-space characters in the RE are ignored, as are

  845.     all characters between a <code class="literal">#</code>

  846.     and the following newline (or the end of the RE).  This

  847.     permits paragraphing and commenting a complex RE.

  848.     There are three exceptions to that basic rule:

  849. 

  850.     </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>

  851.        a white-space character or <code class="literal">#</code> preceded by <code class="literal">\</code> is

  852.        retained

  853.       </p></li><li class="listitem"><p>

  854.        white space or <code class="literal">#</code> within a bracket expression is retained

  855.       </p></li><li class="listitem"><p>

  856.        white space and comments cannot appear within multi-character symbols,

  857.        such as <code class="literal">(?:</code>

  858.       </p></li></ul></div><p>

  859. 

  860.     For this purpose, white-space characters are blank, tab, newline, and

  861.     any character that belongs to the <em class="replaceable"><code>space</code></em> character class.

  862.    </p><p>

  863.     Finally, in an ARE, outside bracket expressions, the sequence

  864.     <code class="literal">(?#</code><em class="replaceable"><code>ttt</code></em><code class="literal">)</code>

  865.     (where <em class="replaceable"><code>ttt</code></em> is any text not containing a <code class="literal">)</code>)

  866.     is a comment, completely ignored.

  867.     Again, this is not allowed between the characters of

  868.     multi-character symbols, like <code class="literal">(?:</code>.

  869.     Such comments are more a historical artifact than a useful facility,

  870.     and their use is deprecated; use the expanded syntax instead.

  871.    </p><p>

  872.     <span class="emphasis"><em>None</em></span> of these metasyntax extensions is available if

  873.     an initial <code class="literal">***=</code> director

  874.     has specified that the user's input be treated as a literal string

  875.     rather than as an RE.

  876.    </p></div><div class="sect3" id="POSIX-MATCHING-RULES"><div class="titlepage"><div><div><h4 class="title">9.7.3.5. Regular Expression Matching Rules</h4></div></div></div><p>

  877.     In the event that an RE could match more than one substring of a given

  878.     string, the RE matches the one starting earliest in the string.

  879.     If the RE could match more than one substring starting at that point,

  880.     either the longest possible match or the shortest possible match will

  881.     be taken, depending on whether the RE is <em class="firstterm">greedy</em> or

  882.     <em class="firstterm">non-greedy</em>.

  883.    </p><p>

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

  885.     </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>

  886.        Most atoms, and all constraints, have no greediness attribute (because

  887.        they cannot match variable amounts of text anyway).

  888.       </p></li><li class="listitem"><p>

  889.        Adding parentheses around an RE does not change its greediness.

  890.       </p></li><li class="listitem"><p>

  891.        A quantified atom with a fixed-repetition quantifier

  892.        (<code class="literal">{</code><em class="replaceable"><code>m</code></em><code class="literal">}</code>

  893.        or

  894.        <code class="literal">{</code><em class="replaceable"><code>m</code></em><code class="literal">}?</code>)

  895.        has the same greediness (possibly none) as the atom itself.

  896.       </p></li><li class="listitem"><p>

  897.        A quantified atom with other normal quantifiers (including

  898.        <code class="literal">{</code><em class="replaceable"><code>m</code></em><code class="literal">,</code><em class="replaceable"><code>n</code></em><code class="literal">}</code>

  899.        with <em class="replaceable"><code>m</code></em> equal to <em class="replaceable"><code>n</code></em>)

  900.        is greedy (prefers longest match).

  901.       </p></li><li class="listitem"><p>

  902.        A quantified atom with a non-greedy quantifier (including

  903.        <code class="literal">{</code><em class="replaceable"><code>m</code></em><code class="literal">,</code><em class="replaceable"><code>n</code></em><code class="literal">}?</code>

  904.        with <em class="replaceable"><code>m</code></em> equal to <em class="replaceable"><code>n</code></em>)

  905.        is non-greedy (prefers shortest match).

  906.       </p></li><li class="listitem"><p>

  907.        A branch — that is, an RE that has no top-level

  908.        <code class="literal">|</code> operator — has the same greediness as the first

  909.        quantified atom in it that has a greediness attribute.

  910.       </p></li><li class="listitem"><p>

  911.        An RE consisting of two or more branches connected by the

  912.        <code class="literal">|</code> operator is always greedy.

  913.       </p></li></ul></div><p>

  914.    </p><p>

  915.     The above rules associate greediness attributes not only with individual

  916.     quantified atoms, but with branches and entire REs that contain quantified

  917.     atoms.  What that means is that the matching is done in such a way that

  918.     the branch, or whole RE, matches the longest or shortest possible

  919.     substring <span class="emphasis"><em>as a whole</em></span>.  Once the length of the entire match

  920.     is determined, the part of it that matches any particular subexpression

  921.     is determined on the basis of the greediness attribute of that

  922.     subexpression, with subexpressions starting earlier in the RE taking

  923.     priority over ones starting later.

  924.    </p><p>

  925.     An example of what this means:

  926. </p><pre class="screen">

  927. SELECT SUBSTRING('XY1234Z', 'Y*([0-9]{1,3})');

  928. <em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">123</code>

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

  930. <em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">1</code>

  931. </pre><p>

  932.     In the first case, the RE as a whole is greedy because <code class="literal">Y*</code>

  933.     is greedy.  It can match beginning at the <code class="literal">Y</code>, and it matches

  934.     the longest possible string starting there, i.e., <code class="literal">Y123</code>.

  935.     The output is the parenthesized part of that, or <code class="literal">123</code>.

  936.     In the second case, the RE as a whole is non-greedy because <code class="literal">Y*?</code>

  937.     is non-greedy.  It can match beginning at the <code class="literal">Y</code>, and it matches

  938.     the shortest possible string starting there, i.e., <code class="literal">Y1</code>.

  939.     The subexpression <code class="literal">[0-9]{1,3}</code> is greedy but it cannot change

  940.     the decision as to the overall match length; so it is forced to match

  941.     just <code class="literal">1</code>.

  942.    </p><p>

  943.     In short, when an RE contains both greedy and non-greedy subexpressions,

  944.     the total match length is either as long as possible or as short as

  945.     possible, according to the attribute assigned to the whole RE.  The

  946.     attributes assigned to the subexpressions only affect how much of that

  947.     match they are allowed to <span class="quote">“<span class="quote">eat</span>”</span> relative to each other.

  948.    </p><p>

  949.     The quantifiers <code class="literal">{1,1}</code> and <code class="literal">{1,1}?</code>

  950.     can be used to force greediness or non-greediness, respectively,

  951.     on a subexpression or a whole RE.

  952.     This is useful when you need the whole RE to have a greediness attribute

  953.     different from what's deduced from its elements.  As an example,

  954.     suppose that we are trying to separate a string containing some digits

  955.     into the digits and the parts before and after them.  We might try to

  956.     do that like this:

  957. </p><pre class="screen">

  958. SELECT regexp_match('abc01234xyz', '(.*)(\d+)(.*)');

  959. <em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">{abc0123,4,xyz}</code>

  960. </pre><p>

  961.     That didn't work: the first <code class="literal">.*</code> is greedy so

  962.     it <span class="quote">“<span class="quote">eats</span>”</span> as much as it can, leaving the <code class="literal">\d+</code> to

  963.     match at the last possible place, the last digit.  We might try to fix

  964.     that by making it non-greedy:

  965. </p><pre class="screen">

  966. SELECT regexp_match('abc01234xyz', '(.*?)(\d+)(.*)');

  967. <em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">{abc,0,""}</code>

  968. </pre><p>

  969.     That didn't work either, because now the RE as a whole is non-greedy

  970.     and so it ends the overall match as soon as possible.  We can get what

  971.     we want by forcing the RE as a whole to be greedy:

  972. </p><pre class="screen">

  973. SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');

  974. <em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">{abc,01234,xyz}</code>

  975. </pre><p>

  976.     Controlling the RE's overall greediness separately from its components'

  977.     greediness allows great flexibility in handling variable-length patterns.

  978.    </p><p>

  979.     When deciding what is a longer or shorter match,

  980.     match lengths are measured in characters, not collating elements.

  981.     An empty string is considered longer than no match at all.

  982.     For example:

  983.     <code class="literal">bb*</code>

  984.     matches the three middle characters of <code class="literal">abbbc</code>;

  985.     <code class="literal">(week|wee)(night|knights)</code>

  986.     matches all ten characters of <code class="literal">weeknights</code>;

  987.     when <code class="literal">(.*).*</code>

  988.     is matched against <code class="literal">abc</code> the parenthesized subexpression

  989.     matches all three characters; and when

  990.     <code class="literal">(a*)*</code> is matched against <code class="literal">bc</code>

  991.     both the whole RE and the parenthesized

  992.     subexpression match an empty string.

  993.    </p><p>

  994.     If case-independent matching is specified,

  995.     the effect is much as if all case distinctions had vanished from the

  996.     alphabet.

  997.     When an alphabetic that exists in multiple cases appears as an

  998.     ordinary character outside a bracket expression, it is effectively

  999.     transformed into a bracket expression containing both cases,

  1000.     e.g., <code class="literal">x</code> becomes <code class="literal">[xX]</code>.

  1001.     When it appears inside a bracket expression, all case counterparts

  1002.     of it are added to the bracket expression, e.g.,

  1003.     <code class="literal">[x]</code> becomes <code class="literal">[xX]</code>

  1004.     and <code class="literal">[^x]</code> becomes <code class="literal">[^xX]</code>.

  1005.    </p><p>

  1006.     If newline-sensitive matching is specified, <code class="literal">.</code>

  1007.     and bracket expressions using <code class="literal">^</code>

  1008.     will never match the newline character

  1009.     (so that matches will never cross newlines unless the RE

  1010.     explicitly arranges it)

  1011.     and <code class="literal">^</code> and <code class="literal">$</code>

  1012.     will match the empty string after and before a newline

  1013.     respectively, in addition to matching at beginning and end of string

  1014.     respectively.

  1015.     But the ARE escapes <code class="literal">\A</code> and <code class="literal">\Z</code>

  1016.     continue to match beginning or end of string <span class="emphasis"><em>only</em></span>.

  1017.    </p><p>

  1018.     If partial newline-sensitive matching is specified,

  1019.     this affects <code class="literal">.</code> and bracket expressions

  1020.     as with newline-sensitive matching, but not <code class="literal">^</code>

  1021.     and <code class="literal">$</code>.

  1022.    </p><p>

  1023.     If inverse partial newline-sensitive matching is specified,

  1024.     this affects <code class="literal">^</code> and <code class="literal">$</code>

  1025.     as with newline-sensitive matching, but not <code class="literal">.</code>

  1026.     and bracket expressions.

  1027.     This isn't very useful but is provided for symmetry.

  1028.    </p></div><div class="sect3" id="POSIX-LIMITS-COMPATIBILITY"><div class="titlepage"><div><div><h4 class="title">9.7.3.6. Limits and Compatibility</h4></div></div></div><p>

  1029.     No particular limit is imposed on the length of REs in this

  1030.     implementation.  However,

  1031.     programs intended to be highly portable should not employ REs longer

  1032.     than 256 bytes,

  1033.     as a POSIX-compliant implementation can refuse to accept such REs.

  1034.    </p><p>

  1035.     The only feature of AREs that is actually incompatible with

  1036.     POSIX EREs is that <code class="literal">\</code> does not lose its special

  1037.     significance inside bracket expressions.

  1038.     All other ARE features use syntax which is illegal or has

  1039.     undefined or unspecified effects in POSIX EREs;

  1040.     the <code class="literal">***</code> syntax of directors likewise is outside the POSIX

  1041.     syntax for both BREs and EREs.

  1042.    </p><p>

  1043.     Many of the ARE extensions are borrowed from Perl, but some have

  1044.     been changed to clean them up, and a few Perl extensions are not present.

  1045.     Incompatibilities of note include <code class="literal">\b</code>, <code class="literal">\B</code>,

  1046.     the lack of special treatment for a trailing newline,

  1047.     the addition of complemented bracket expressions to the things

  1048.     affected by newline-sensitive matching,

  1049.     the restrictions on parentheses and back references in lookahead/lookbehind

  1050.     constraints, and the longest/shortest-match (rather than first-match)

  1051.     matching semantics.

  1052.    </p><p>

  1053.     Two significant incompatibilities exist between AREs and the ERE syntax

  1054.     recognized by pre-7.4 releases of <span class="productname">PostgreSQL</span>:

  1055. 

  1056.     </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>

  1057.        In AREs, <code class="literal">\</code> followed by an alphanumeric character is either

  1058.        an escape or an error, while in previous releases, it was just another

  1059.        way of writing the alphanumeric.

  1060.        This should not be much of a problem because there was no reason to

  1061.        write such a sequence in earlier releases.

  1062.       </p></li><li class="listitem"><p>

  1063.        In AREs, <code class="literal">\</code> remains a special character within

  1064.        <code class="literal">[]</code>, so a literal <code class="literal">\</code> within a bracket

  1065.        expression must be written <code class="literal">\\</code>.

  1066.       </p></li></ul></div><p>

  1067.    </p></div><div class="sect3" id="POSIX-BASIC-REGEXES"><div class="titlepage"><div><div><h4 class="title">9.7.3.7. Basic Regular Expressions</h4></div></div></div><p>

  1068.     BREs differ from EREs in several respects.

  1069.     In BREs, <code class="literal">|</code>, <code class="literal">+</code>, and <code class="literal">?</code>

  1070.     are ordinary characters and there is no equivalent

  1071.     for their functionality.

  1072.     The delimiters for bounds are

  1073.     <code class="literal">\{</code> and <code class="literal">\}</code>,

  1074.     with <code class="literal">{</code> and <code class="literal">}</code>

  1075.     by themselves ordinary characters.

  1076.     The parentheses for nested subexpressions are

  1077.     <code class="literal">\(</code> and <code class="literal">\)</code>,

  1078.     with <code class="literal">(</code> and <code class="literal">)</code> by themselves ordinary characters.

  1079.     <code class="literal">^</code> is an ordinary character except at the beginning of the

  1080.     RE or the beginning of a parenthesized subexpression,

  1081.     <code class="literal">$</code> is an ordinary character except at the end of the

  1082.     RE or the end of a parenthesized subexpression,

  1083.     and <code class="literal">*</code> is an ordinary character if it appears at the beginning

  1084.     of the RE or the beginning of a parenthesized subexpression

  1085.     (after a possible leading <code class="literal">^</code>).

  1086.     Finally, single-digit back references are available, and

  1087.     <code class="literal">\&lt;</code> and <code class="literal">\&gt;</code>

  1088.     are synonyms for

  1089.     <code class="literal">[[:&lt;:]]</code> and <code class="literal">[[:&gt;:]]</code>

  1090.     respectively; no other escapes are available in BREs.

  1091.    </p></div><div class="sect3" id="POSIX-VS-XQUERY"><div class="titlepage"><div><div><h4 class="title">9.7.3.8. Differences From XQuery (<code class="literal">LIKE_REGEX</code>)</h4></div></div></div><a id="id-1.5.8.12.9.35.2" class="indexterm"></a><a id="id-1.5.8.12.9.35.3" class="indexterm"></a><p>

  1092.      Since SQL:2008, the SQL standard includes

  1093.      a <code class="literal">LIKE_REGEX</code> operator that performs pattern

  1094.      matching according to the XQuery regular expression

  1095.      standard.  <span class="productname">PostgreSQL</span> does not yet

  1096.      implement this operator, but you can get very similar behavior using

  1097.      the <code class="function">regexp_match()</code> function, since XQuery

  1098.      regular expressions are quite close to the ARE syntax described above.

  1099.     </p><p>

  1100.      Notable differences between the existing POSIX-based

  1101.      regular-expression feature and XQuery regular expressions include:

  1102. 

  1103.      </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>

  1104.         XQuery character class subtraction is not supported.  An example of

  1105.         this feature is using the following to match only English

  1106.         consonants: <code class="literal">[a-z-[aeiou]]</code>.

  1107.        </p></li><li class="listitem"><p>

  1108.         XQuery character class shorthands <code class="literal">\c</code>,

  1109.         <code class="literal">\C</code>, <code class="literal">\i</code>,

  1110.         and <code class="literal">\I</code> are not supported.

  1111.        </p></li><li class="listitem"><p>

  1112.         XQuery character class elements

  1113.         using <code class="literal">\p{UnicodeProperty}</code> or the

  1114.         inverse <code class="literal">\P{UnicodeProperty}</code> are not supported.

  1115.        </p></li><li class="listitem"><p>

  1116.         POSIX interprets character classes such as <code class="literal">\w</code>

  1117.         (see <a class="xref" href="functions-matching.html#POSIX-CLASS-SHORTHAND-ESCAPES-TABLE" title="Table 9.20. Regular Expression Class-Shorthand Escapes">Table 9.20</a>)

  1118.         according to the prevailing locale (which you can control by

  1119.         attaching a <code class="literal">COLLATE</code> clause to the operator or

  1120.         function).  XQuery specifies these classes by reference to Unicode

  1121.         character properties, so equivalent behavior is obtained only with

  1122.         a locale that follows the Unicode rules.

  1123.        </p></li><li class="listitem"><p>

  1124.         The SQL standard (not XQuery itself) attempts to cater for more

  1125.         variants of <span class="quote">“<span class="quote">newline</span>”</span> than POSIX does.  The

  1126.         newline-sensitive matching options described above consider only

  1127.         ASCII NL (<code class="literal">\n</code>) to be a newline, but SQL would have

  1128.         us treat CR (<code class="literal">\r</code>), CRLF (<code class="literal">\r\n</code>)

  1129.         (a Windows-style newline), and some Unicode-only characters like

  1130.         LINE SEPARATOR (U+2028) as newlines as well.

  1131.         Notably, <code class="literal">.</code> and <code class="literal">\s</code> should

  1132.         count <code class="literal">\r\n</code> as one character not two according to

  1133.         SQL.

  1134.        </p></li><li class="listitem"><p>

  1135.         Of the character-entry escapes described in

  1136.         <a class="xref" href="functions-matching.html#POSIX-CHARACTER-ENTRY-ESCAPES-TABLE" title="Table 9.19. Regular Expression Character-Entry Escapes">Table 9.19</a>,

  1137.         XQuery supports only <code class="literal">\n</code>, <code class="literal">\r</code>,

  1138.         and <code class="literal">\t</code>.

  1139.        </p></li><li class="listitem"><p>

  1140.         XQuery does not support

  1141.         the <code class="literal">[:<em class="replaceable"><code>name</code></em>:]</code> syntax

  1142.         for character classes within bracket expressions.

  1143.        </p></li><li class="listitem"><p>

  1144.         XQuery does not have lookahead or lookbehind constraints,

  1145.         nor any of the constraint escapes described in

  1146.         <a class="xref" href="functions-matching.html#POSIX-CONSTRAINT-ESCAPES-TABLE" title="Table 9.21. Regular Expression Constraint Escapes">Table 9.21</a>.

  1147.        </p></li><li class="listitem"><p>

  1148.         The metasyntax forms described in <a class="xref" href="functions-matching.html#POSIX-METASYNTAX" title="9.7.3.4. Regular Expression Metasyntax">Section 9.7.3.4</a>

  1149.         do not exist in XQuery.

  1150.        </p></li><li class="listitem"><p>

  1151.         The regular expression flag letters defined by XQuery are

  1152.         related to but not the same as the option letters for POSIX

  1153.         (<a class="xref" href="functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE" title="Table 9.23. ARE Embedded-Option Letters">Table 9.23</a>).  While the

  1154.         <code class="literal">i</code> and <code class="literal">q</code> options behave the

  1155.         same, others do not:

  1156.         </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>

  1157.            XQuery's <code class="literal">s</code> (allow dot to match newline)

  1158.            and <code class="literal">m</code> (allow <code class="literal">^</code>

  1159.            and <code class="literal">$</code> to match at newlines) flags provide

  1160.            access to the same behaviors as

  1161.            POSIX's <code class="literal">n</code>, <code class="literal">p</code>

  1162.            and <code class="literal">w</code> flags, but they

  1163.            do <span class="emphasis"><em>not</em></span> match the behavior of

  1164.            POSIX's <code class="literal">s</code> and <code class="literal">m</code> flags.

  1165.            Note in particular that dot-matches-newline is the default

  1166.            behavior in POSIX but not XQuery.

  1167.           </p></li><li class="listitem"><p>

  1168.            XQuery's <code class="literal">x</code> (ignore whitespace in pattern) flag

  1169.            is noticeably different from POSIX's expanded-mode flag.

  1170.            POSIX's <code class="literal">x</code> flag also

  1171.            allows <code class="literal">#</code> to begin a comment in the pattern,

  1172.            and POSIX will not ignore a whitespace character after a

  1173.            backslash.

  1174.           </p></li></ul></div><p>

  1175.        </p></li></ul></div><p>

  1176.     </p></div></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-bitstring.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-formatting.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.6. Bit String Functions and Operators </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 9.8. Data Type Formatting Functions</td></tr></table></div></body></html>
上海开阖软件有限公司 沪ICP备12045867号-1