Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
functions-matching.html1415 linesDownload Raw Back to html
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 Vsnapshot" /><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 id="docContent" class="container-fluid col-10"><div 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 16.3 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 /></div><div class="sect1" id="FUNCTIONS-MATCHING"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.7. Pattern Matching <a href="#FUNCTIONS-MATCHING" class="id_link">#</a></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.13.2" class="indexterm"></a><p>3    There are three separate approaches to pattern matching provided4    by <span class="productname">PostgreSQL</span>: the traditional5    <acronym class="acronym">SQL</acronym> <code class="function">LIKE</code> operator, the6    more recent <code class="function">SIMILAR TO</code> operator (added in7    SQL:1999), and <acronym class="acronym">POSIX</acronym>-style regular8    expressions.  Aside from the basic <span class="quote">“<span class="quote">does this string match9    this pattern?</span>”</span> operators, functions are available to extract10    or replace matching substrings and to split a string at matching11    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 of18     time and memory to process.  Be wary of accepting regular-expression19     search patterns from hostile sources.  If you must do so, it is20     advisable to impose a statement timeout.21    </p><p>22     Searches using <code class="function">SIMILAR TO</code> patterns have the same23     security hazards, since <code class="function">SIMILAR TO</code> provides many24     of the same capabilities as <acronym class="acronym">POSIX</acronym>-style regular25     expressions.26    </p><p>27     <code class="function">LIKE</code> searches, being much simpler than the other28     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 support31    nondeterministic collations.  If required, apply a different collation to32    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> <a href="#FUNCTIONS-LIKE" class="id_link">#</a></h3></div></div></div><a id="id-1.5.8.13.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 the38     <em class="replaceable"><code>string</code></em> matches the supplied39     <em class="replaceable"><code>pattern</code></em>.  (As40     expected, the <code class="function">NOT LIKE</code> expression returns41     false if <code class="function">LIKE</code> returns true, and vice versa.42     An equivalent expression is43     <code class="literal">NOT (<em class="replaceable"><code>string</code></em> LIKE44      <em class="replaceable"><code>pattern</code></em>)</code>.)45    </p><p>46     If <em class="replaceable"><code>pattern</code></em> does not contain percent47     signs or underscores, then the pattern only represents the string48     itself; in that case <code class="function">LIKE</code> acts like the49     equals operator.  An underscore (<code class="literal">_</code>) in50     <em class="replaceable"><code>pattern</code></em> stands for (matches) any single51     character; a percent sign (<code class="literal">%</code>) matches any sequence52     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 entire63    string.  Therefore, if it's desired to match a sequence anywhere within64    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 matching67    other characters, the respective character in68    <em class="replaceable"><code>pattern</code></em> must be69    preceded by the escape character.  The default escape70    character is the backslash but a different one can be selected by71    using the <code class="literal">ESCAPE</code> clause.  To match the escape72    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 be76     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 writing79    <code class="literal">ESCAPE ''</code>.  This effectively disables the80    escape mechanism, which makes it impossible to turn off the81    special meaning of underscore and percent signs in the pattern.82   </p><p>83    According to the SQL standard, omitting <code class="literal">ESCAPE</code>84    means there is no escape character (rather than defaulting to a85    backslash), and a zero-length <code class="literal">ESCAPE</code> value is86    disallowed.  <span class="productname">PostgreSQL</span>'s behavior in87    this regard is therefore slightly nonstandard.88   </p><p>89    The key word <code class="token">ILIKE</code> can be used instead of90    <code class="token">LIKE</code> to make the match case-insensitive according91    to the active locale.  This is not in the <acronym class="acronym">SQL</acronym> standard but is a92    <span class="productname">PostgreSQL</span> extension.93   </p><p>94    The operator <code class="literal">~~</code> is equivalent to95    <code class="function">LIKE</code>, and <code class="literal">~~*</code> corresponds to96    <code class="function">ILIKE</code>.  There are also97    <code class="literal">!~~</code> and <code class="literal">!~~*</code> operators that98    represent <code class="function">NOT LIKE</code> and <code class="function">NOT99    ILIKE</code>, respectively.  All of these operators are100    <span class="productname">PostgreSQL</span>-specific.  You may see these101    operator names in <code class="command">EXPLAIN</code> output and similar102    places, since the parser actually translates <code class="function">LIKE</code>103    et al. to these operators.104   </p><p>105    The phrases <code class="function">LIKE</code>, <code class="function">ILIKE</code>,106    <code class="function">NOT LIKE</code>, and <code class="function">NOT ILIKE</code> are107    generally treated as operators108    in <span class="productname">PostgreSQL</span> syntax; for example they can109    be used in <em class="replaceable"><code>expression</code></em>110    <em class="replaceable"><code>operator</code></em> ANY111    (<em class="replaceable"><code>subquery</code></em>) constructs, although112    an <code class="literal">ESCAPE</code> clause cannot be included there.  In some113    obscure cases it may be necessary to use the underlying operator names114    instead.115   </p><p>116    Also see the starts-with operator <code class="literal">^@</code> and the117    corresponding <code class="function">starts_with()</code> function, which are118    useful in cases where simply matching the beginning of a string is119    needed.120   </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 <a href="#FUNCTIONS-SIMILARTO-REGEXP" class="id_link">#</a></h3></div></div></div><a id="id-1.5.8.13.8.2" class="indexterm"></a><a id="id-1.5.8.13.8.3" class="indexterm"></a><a id="id-1.5.8.13.8.4" class="indexterm"></a><pre class="synopsis">121<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>]122<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>]123</pre><p>124    The <code class="function">SIMILAR TO</code> operator returns true or125    false depending on whether its pattern matches the given string.126    It is similar to <code class="function">LIKE</code>, except that it127    interprets the pattern using the SQL standard's definition of a128    regular expression.  SQL regular expressions are a curious cross129    between <code class="function">LIKE</code> notation and common (POSIX) regular130    expression notation.131   </p><p>132    Like <code class="function">LIKE</code>, the <code class="function">SIMILAR TO</code>133    operator succeeds only if its pattern matches the entire string;134    this is unlike common regular expression behavior where the pattern135    can match any part of the string.136    Also like137    <code class="function">LIKE</code>, <code class="function">SIMILAR TO</code> uses138    <code class="literal">_</code> and <code class="literal">%</code> as wildcard characters denoting139    any single character and any string, respectively (these are140    comparable to <code class="literal">.</code> and <code class="literal">.*</code> in POSIX regular141    expressions).142   </p><p>143    In addition to these facilities borrowed from <code class="function">LIKE</code>,144    <code class="function">SIMILAR TO</code> supports these pattern-matching145    metacharacters borrowed from POSIX regular expressions:146 147   </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>148      <code class="literal">|</code> denotes alternation (either of two alternatives).149     </p></li><li class="listitem"><p>150      <code class="literal">*</code> denotes repetition of the previous item zero151      or more times.152     </p></li><li class="listitem"><p>153      <code class="literal">+</code> denotes repetition of the previous item one154      or more times.155     </p></li><li class="listitem"><p>156      <code class="literal">?</code> denotes repetition of the previous item zero157      or one time.158     </p></li><li class="listitem"><p>159      <code class="literal">{</code><em class="replaceable"><code>m</code></em><code class="literal">}</code> denotes repetition160      of the previous item exactly <em class="replaceable"><code>m</code></em> times.161     </p></li><li class="listitem"><p>162      <code class="literal">{</code><em class="replaceable"><code>m</code></em><code class="literal">,}</code> denotes repetition163      of the previous item <em class="replaceable"><code>m</code></em> or more times.164     </p></li><li class="listitem"><p>165      <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>166      denotes repetition of the previous item at least <em class="replaceable"><code>m</code></em> and167      not more than <em class="replaceable"><code>n</code></em> times.168     </p></li><li class="listitem"><p>169      Parentheses <code class="literal">()</code> can be used to group items into170      a single logical item.171     </p></li><li class="listitem"><p>172      A bracket expression <code class="literal">[...]</code> specifies a character173      class, just as in POSIX regular expressions.174     </p></li></ul></div><p>175 176    Notice that the period (<code class="literal">.</code>) is not a metacharacter177    for <code class="function">SIMILAR TO</code>.178   </p><p>179    As with <code class="function">LIKE</code>, a backslash disables the special180    meaning of any of these metacharacters.  A different escape character181    can be specified with <code class="literal">ESCAPE</code>, or the escape182    capability can be disabled by writing <code class="literal">ESCAPE ''</code>.183   </p><p>184    According to the SQL standard, omitting <code class="literal">ESCAPE</code>185    means there is no escape character (rather than defaulting to a186    backslash), and a zero-length <code class="literal">ESCAPE</code> value is187    disallowed.  <span class="productname">PostgreSQL</span>'s behavior in188    this regard is therefore slightly nonstandard.189   </p><p>190    Another nonstandard extension is that following the escape character191    with a letter or digit provides access to the escape sequences192    defined for POSIX regular expressions; see193    <a class="xref" href="functions-matching.html#POSIX-CHARACTER-ENTRY-ESCAPES-TABLE" title="Table 9.20. Regular Expression Character-Entry Escapes">Table 9.20</a>,194    <a class="xref" href="functions-matching.html#POSIX-CLASS-SHORTHAND-ESCAPES-TABLE" title="Table 9.21. Regular Expression Class-Shorthand Escapes">Table 9.21</a>, and195    <a class="xref" href="functions-matching.html#POSIX-CONSTRAINT-ESCAPES-TABLE" title="Table 9.22. Regular Expression Constraint Escapes">Table 9.22</a> below.196   </p><p>197    Some examples:198</p><pre class="programlisting">199'abc' SIMILAR TO 'abc'          <em class="lineannotation"><span class="lineannotation">true</span></em>200'abc' SIMILAR TO 'a'            <em class="lineannotation"><span class="lineannotation">false</span></em>201'abc' SIMILAR TO '%(b|d)%'      <em class="lineannotation"><span class="lineannotation">true</span></em>202'abc' SIMILAR TO '(b|c)%'       <em class="lineannotation"><span class="lineannotation">false</span></em>203'-abc-' SIMILAR TO '%\mabc\M%'  <em class="lineannotation"><span class="lineannotation">true</span></em>204'xabcy' SIMILAR TO '%\mabc\M%'  <em class="lineannotation"><span class="lineannotation">false</span></em>205</pre><p>206   </p><p>207    The <code class="function">substring</code> function with three parameters208    provides extraction of a substring that matches an SQL209    regular expression pattern.  The function can be written according210    to standard SQL syntax:211</p><pre class="synopsis">212substring(<em class="replaceable"><code>string</code></em> similar <em class="replaceable"><code>pattern</code></em> escape <em class="replaceable"><code>escape-character</code></em>)213</pre><p>214    or using the now obsolete SQL:1999 syntax:215</p><pre class="synopsis">216substring(<em class="replaceable"><code>string</code></em> from <em class="replaceable"><code>pattern</code></em> for <em class="replaceable"><code>escape-character</code></em>)217</pre><p>218    or as a plain three-argument function:219</p><pre class="synopsis">220substring(<em class="replaceable"><code>string</code></em>, <em class="replaceable"><code>pattern</code></em>, <em class="replaceable"><code>escape-character</code></em>)221</pre><p>222    As with <code class="literal">SIMILAR TO</code>, the223    specified pattern must match the entire data string, or else the224    function fails and returns null.  To indicate the part of the225    pattern for which the matching data sub-string is of interest,226    the pattern should contain227    two occurrences of the escape character followed by a double quote228    (<code class="literal">"</code>). 229    The text matching the portion of the pattern230    between these separators is returned when the match is successful.231   </p><p>232    The escape-double-quote separators actually233    divide <code class="function">substring</code>'s pattern into three independent234    regular expressions; for example, a vertical bar (<code class="literal">|</code>)235    in any of the three sections affects only that section.  Also, the first236    and third of these regular expressions are defined to match the smallest237    possible amount of text, not the largest, when there is any ambiguity238    about how much of the data string matches which pattern.  (In POSIX239    parlance, the first and third regular expressions are forced to be240    non-greedy.)241   </p><p>242    As an extension to the SQL standard, <span class="productname">PostgreSQL</span>243    allows there to be just one escape-double-quote separator, in which case244    the third regular expression is taken as empty; or no separators, in which245    case the first and third regular expressions are taken as empty.246   </p><p>247    Some examples, with <code class="literal">#"</code> delimiting the return string:248</p><pre class="programlisting">249substring('foobar' similar '%#"o_b#"%' escape '#')   <em class="lineannotation"><span class="lineannotation">oob</span></em>250substring('foobar' similar '#"o_b#"%' escape '#')    <em class="lineannotation"><span class="lineannotation">NULL</span></em>251</pre><p>252   </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 <a href="#FUNCTIONS-POSIX-REGEXP" class="id_link">#</a></h3></div></div></div><a id="id-1.5.8.13.9.2" class="indexterm"></a><a id="id-1.5.8.13.9.3" class="indexterm"></a><a id="id-1.5.8.13.9.4" class="indexterm"></a><a id="id-1.5.8.13.9.5" class="indexterm"></a><a id="id-1.5.8.13.9.6" class="indexterm"></a><a id="id-1.5.8.13.9.7" class="indexterm"></a><a id="id-1.5.8.13.9.8" class="indexterm"></a><a id="id-1.5.8.13.9.9" class="indexterm"></a><a id="id-1.5.8.13.9.10" class="indexterm"></a><a id="id-1.5.8.13.9.11" class="indexterm"></a><a id="id-1.5.8.13.9.12" class="indexterm"></a><p>253    <a class="xref" href="functions-matching.html#FUNCTIONS-POSIX-TABLE" title="Table 9.16. Regular Expression Match Operators">Table 9.16</a> lists the available254    operators for pattern matching using POSIX regular expressions.255   </p><div class="table" id="FUNCTIONS-POSIX-TABLE"><p class="title"><strong>Table 9.16. Regular Expression Match Operators</strong></p><div class="table-contents"><table class="table" summary="Regular Expression Match Operators" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">256        Operator257       </p>258       <p>259        Description260       </p>261       <p>262        Example(s)263       </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">264        <code class="type">text</code> <code class="literal">~</code> <code class="type">text</code>265        → <code class="returnvalue">boolean</code>266       </p>267       <p>268        String matches regular expression, case sensitively269       </p>270       <p>271        <code class="literal">'thomas' ~ 't.*ma'</code>272        → <code class="returnvalue">t</code>273       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">274        <code class="type">text</code> <code class="literal">~*</code> <code class="type">text</code>275        → <code class="returnvalue">boolean</code>276       </p>277       <p>278        String matches regular expression, case-insensitively279       </p>280       <p>281        <code class="literal">'thomas' ~* 'T.*ma'</code>282        → <code class="returnvalue">t</code>283       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">284        <code class="type">text</code> <code class="literal">!~</code> <code class="type">text</code>285        → <code class="returnvalue">boolean</code>286       </p>287       <p>288        String does not match regular expression, case sensitively289       </p>290       <p>291        <code class="literal">'thomas' !~ 't.*max'</code>292        → <code class="returnvalue">t</code>293       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">294        <code class="type">text</code> <code class="literal">!~*</code> <code class="type">text</code>295        → <code class="returnvalue">boolean</code>296       </p>297       <p>298        String does not match regular expression, case-insensitively299       </p>300       <p>301        <code class="literal">'thomas' !~* 'T.*ma'</code>302        → <code class="returnvalue">f</code>303       </p></td></tr></tbody></table></div></div><br class="table-break" /><p>304     <acronym class="acronym">POSIX</acronym> regular expressions provide a more305     powerful means for pattern matching than the <code class="function">LIKE</code> and306     <code class="function">SIMILAR TO</code> operators.307     Many Unix tools such as <code class="command">egrep</code>,308     <code class="command">sed</code>, or <code class="command">awk</code> use a pattern309     matching language that is similar to the one described here.310    </p><p>311     A regular expression is a character sequence that is an312     abbreviated definition of a set of strings (a <em class="firstterm">regular313     set</em>).  A string is said to match a regular expression314     if it is a member of the regular set described by the regular315     expression.  As with <code class="function">LIKE</code>, pattern characters316     match string characters exactly unless they are special characters317     in the regular expression language — but regular expressions use318     different special characters than <code class="function">LIKE</code> does.319     Unlike <code class="function">LIKE</code> patterns, a320     regular expression is allowed to match anywhere within a string, unless321     the regular expression is explicitly anchored to the beginning or322     end of the string.323    </p><p>324     Some examples:325</p><pre class="programlisting">326'abcd' ~ 'bc'     <em class="lineannotation"><span class="lineannotation">true</span></em>327'abcd' ~ 'a.c'    <em class="lineannotation"><span class="lineannotation">true — dot matches any character</span></em>328'abcd' ~ 'a.*d'   <em class="lineannotation"><span class="lineannotation">true — <code class="literal">*</code> repeats the preceding pattern item</span></em>329'abcd' ~ '(b|x)'  <em class="lineannotation"><span class="lineannotation">true — <code class="literal">|</code> means OR, parentheses group</span></em>330'abcd' ~ '^a'     <em class="lineannotation"><span class="lineannotation">true — <code class="literal">^</code> anchors to start of string</span></em>331'abcd' ~ '^(b|c)' <em class="lineannotation"><span class="lineannotation">false — would match except for anchoring</span></em>332</pre><p>333    </p><p>334     The <acronym class="acronym">POSIX</acronym> pattern language is described in much335     greater detail below.336    </p><p>337     The <code class="function">substring</code> function with two parameters,338     <code class="function">substring(<em class="replaceable"><code>string</code></em> from339     <em class="replaceable"><code>pattern</code></em>)</code>, provides extraction of a340     substring341     that matches a POSIX regular expression pattern.  It returns null if342     there is no match, otherwise the first portion of the text that matched the343     pattern.  But if the pattern contains any parentheses, the portion344     of the text that matched the first parenthesized subexpression (the345     one whose left parenthesis comes first) is346     returned.  You can put parentheses around the whole expression347     if you want to use parentheses within it without triggering this348     exception.  If you need parentheses in the pattern before the349     subexpression you want to extract, see the non-capturing parentheses350     described below.351    </p><p>352    Some examples:353</p><pre class="programlisting">354substring('foobar' from 'o.b')     <em class="lineannotation"><span class="lineannotation">oob</span></em>355substring('foobar' from 'o(.)b')   <em class="lineannotation"><span class="lineannotation">o</span></em>356</pre><p>357   </p><p>358     The <code class="function">regexp_count</code> function counts the number of359     places where a POSIX regular expression pattern matches a string.360     It has the syntax361     <code class="function">regexp_count</code>(<em class="replaceable"><code>string</code></em>,362     <em class="replaceable"><code>pattern</code></em>363     [<span class="optional">, <em class="replaceable"><code>start</code></em>364     [<span class="optional">, <em class="replaceable"><code>flags</code></em>365     </span>]</span>]).366     <em class="replaceable"><code>pattern</code></em> is searched for367     in <em class="replaceable"><code>string</code></em>, normally from the beginning of368     the string, but if the <em class="replaceable"><code>start</code></em> parameter is369     provided then beginning from that character index.370     The <em class="replaceable"><code>flags</code></em> parameter is an optional text371     string containing zero or more single-letter flags that change the372     function's behavior.  For example, including <code class="literal">i</code> in373     <em class="replaceable"><code>flags</code></em> specifies case-insensitive matching.374     Supported flags are described in375     <a class="xref" href="functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE" title="Table 9.24. ARE Embedded-Option Letters">Table 9.24</a>.376    </p><p>377     Some examples:378</p><pre class="programlisting">379regexp_count('ABCABCAXYaxy', 'A.')          <em class="lineannotation"><span class="lineannotation">3</span></em>380regexp_count('ABCABCAXYaxy', 'A.', 1, 'i')  <em class="lineannotation"><span class="lineannotation">4</span></em>381</pre><p>382    </p><p>383     The <code class="function">regexp_instr</code> function returns the starting or384     ending position of the <em class="replaceable"><code>N</code></em>'th match of a385     POSIX regular expression pattern to a string, or zero if there is no386     such match.  It has the syntax387     <code class="function">regexp_instr</code>(<em class="replaceable"><code>string</code></em>,388     <em class="replaceable"><code>pattern</code></em>389     [<span class="optional">, <em class="replaceable"><code>start</code></em>390     [<span class="optional">, <em class="replaceable"><code>N</code></em>391     [<span class="optional">, <em class="replaceable"><code>endoption</code></em>392     [<span class="optional">, <em class="replaceable"><code>flags</code></em>393     [<span class="optional">, <em class="replaceable"><code>subexpr</code></em>394     </span>]</span>]</span>]</span>]</span>]).395     <em class="replaceable"><code>pattern</code></em> is searched for396     in <em class="replaceable"><code>string</code></em>, normally from the beginning of397     the string, but if the <em class="replaceable"><code>start</code></em> parameter is398     provided then beginning from that character index.399     If <em class="replaceable"><code>N</code></em> is specified400     then the <em class="replaceable"><code>N</code></em>'th match of the pattern401     is located, otherwise the first match is located.402     If the <em class="replaceable"><code>endoption</code></em> parameter is omitted or403     specified as zero, the function returns the position of the first404     character of the match.  Otherwise, <em class="replaceable"><code>endoption</code></em>405     must be one, and the function returns the position of the character406     following the match.407     The <em class="replaceable"><code>flags</code></em> parameter is an optional text408     string containing zero or more single-letter flags that change the409     function's behavior.  Supported flags are described410     in <a class="xref" href="functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE" title="Table 9.24. ARE Embedded-Option Letters">Table 9.24</a>.411     For a pattern containing parenthesized412     subexpressions, <em class="replaceable"><code>subexpr</code></em> is an integer413     indicating which subexpression is of interest: the result identifies414     the position of the substring matching that subexpression.415     Subexpressions are numbered in the order of their leading parentheses.416     When <em class="replaceable"><code>subexpr</code></em> is omitted or zero, the result417     identifies the position of the whole match regardless of418     parenthesized subexpressions.419    </p><p>420     Some examples:421</p><pre class="programlisting">422regexp_instr('number of your street, town zip, FR', '[^,]+', 1, 2)423                                   <em class="lineannotation"><span class="lineannotation">23</span></em>424regexp_instr('ABCDEFGHI', '(c..)(...)', 1, 1, 0, 'i', 2)425                                   <em class="lineannotation"><span class="lineannotation">6</span></em>426</pre><p>427    </p><p>428     The <code class="function">regexp_like</code> function checks whether a match429     of a POSIX regular expression pattern occurs within a string,430     returning boolean true or false.  It has the syntax431     <code class="function">regexp_like</code>(<em class="replaceable"><code>string</code></em>,432     <em class="replaceable"><code>pattern</code></em>433     [<span class="optional">, <em class="replaceable"><code>flags</code></em> </span>]).434     The <em class="replaceable"><code>flags</code></em> parameter is an optional text435     string containing zero or more single-letter flags that change the436     function's behavior.  Supported flags are described437     in <a class="xref" href="functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE" title="Table 9.24. ARE Embedded-Option Letters">Table 9.24</a>.438     This function has the same results as the <code class="literal">~</code>439     operator if no flags are specified.  If only the <code class="literal">i</code>440     flag is specified, it has the same results as441     the <code class="literal">~*</code> operator.442    </p><p>443     Some examples:444</p><pre class="programlisting">445regexp_like('Hello World', 'world')       <em class="lineannotation"><span class="lineannotation">false</span></em>446regexp_like('Hello World', 'world', 'i')  <em class="lineannotation"><span class="lineannotation">true</span></em>447</pre><p>448    </p><p>449     The <code class="function">regexp_match</code> function returns a text array of450     matching substring(s) within the first match of a POSIX451     regular expression pattern to a string.  It has the syntax452     <code class="function">regexp_match</code>(<em class="replaceable"><code>string</code></em>,453     <em class="replaceable"><code>pattern</code></em> [<span class="optional">, <em class="replaceable"><code>flags</code></em> </span>]).454     If there is no match, the result is <code class="literal">NULL</code>.455     If a match is found, and the <em class="replaceable"><code>pattern</code></em> contains no456     parenthesized subexpressions, then the result is a single-element text457     array containing the substring matching the whole pattern.458     If a match is found, and the <em class="replaceable"><code>pattern</code></em> contains459     parenthesized subexpressions, then the result is a text array460     whose <em class="replaceable"><code>n</code></em>'th element is the substring matching461     the <em class="replaceable"><code>n</code></em>'th parenthesized subexpression of462     the <em class="replaceable"><code>pattern</code></em> (not counting <span class="quote">“<span class="quote">non-capturing</span>”</span>463     parentheses; see below for details).464     The <em class="replaceable"><code>flags</code></em> parameter is an optional text string465     containing zero or more single-letter flags that change the function's466     behavior.  Supported flags are described467     in <a class="xref" href="functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE" title="Table 9.24. ARE Embedded-Option Letters">Table 9.24</a>.468    </p><p>469    Some examples:470</p><pre class="programlisting">471SELECT regexp_match('foobarbequebaz', 'bar.*que');472 regexp_match473--------------474 {barbeque}475(1 row)476 477SELECT regexp_match('foobarbequebaz', '(bar)(beque)');478 regexp_match479--------------480 {bar,beque}481(1 row)482</pre><p>483   </p><div class="tip"><h3 class="title">Tip</h3><p>484      In the common case where you just want the whole matching substring485      or <code class="literal">NULL</code> for no match, the best solution is to486      use <code class="function">regexp_substr()</code>.487      However, <code class="function">regexp_substr()</code> only exists488      in <span class="productname">PostgreSQL</span> version 15 and up.  When489      working in older versions, you can extract the first element490      of <code class="function">regexp_match()</code>'s result, for example:491</p><pre class="programlisting">492SELECT (regexp_match('foobarbequebaz', 'bar.*que'))[1];493 regexp_match494--------------495 barbeque496(1 row)497</pre><p>498     </p></div><p>499     The <code class="function">regexp_matches</code> function returns a set of text arrays500     of matching substring(s) within matches of a POSIX regular501     expression pattern to a string.  It has the same syntax as502     <code class="function">regexp_match</code>.503     This function returns no rows if there is no match, one row if there is504     a match and the <code class="literal">g</code> flag is not given, or <em class="replaceable"><code>N</code></em>505     rows if there are <em class="replaceable"><code>N</code></em> matches and the <code class="literal">g</code> flag506     is given.  Each returned row is a text array containing the whole507     matched substring or the substrings matching parenthesized508     subexpressions of the <em class="replaceable"><code>pattern</code></em>, just as described above509     for <code class="function">regexp_match</code>.510     <code class="function">regexp_matches</code> accepts all the flags shown511     in <a class="xref" href="functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE" title="Table 9.24. ARE Embedded-Option Letters">Table 9.24</a>, plus512     the <code class="literal">g</code> flag which commands it to return all matches, not513     just the first one.514    </p><p>515    Some examples:516</p><pre class="programlisting">517SELECT regexp_matches('foo', 'not there');518 regexp_matches519----------------520(0 rows)521 522SELECT regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)', 'g');523 regexp_matches524----------------525 {bar,beque}526 {bazil,barf}527(2 rows)528</pre><p>529   </p><div class="tip"><h3 class="title">Tip</h3><p>530     In most cases <code class="function">regexp_matches()</code> should be used with531     the <code class="literal">g</code> flag, since if you only want the first match, it's532     easier and more efficient to use <code class="function">regexp_match()</code>.533     However, <code class="function">regexp_match()</code> only exists534     in <span class="productname">PostgreSQL</span> version 10 and up.  When working in older535     versions, a common trick is to place a <code class="function">regexp_matches()</code>536     call in a sub-select, for example:537</p><pre class="programlisting">538SELECT col1, (SELECT regexp_matches(col2, '(bar)(beque)')) FROM tab;539</pre><p>540     This produces a text array if there's a match, or <code class="literal">NULL</code> if541     not, the same as <code class="function">regexp_match()</code> would do.  Without the542     sub-select, this query would produce no output at all for table rows543     without a match, which is typically not the desired behavior.544    </p></div><p>545     The <code class="function">regexp_replace</code> function provides substitution of546     new text for substrings that match POSIX regular expression patterns.547     It has the syntax548     <code class="function">regexp_replace</code>(<em class="replaceable"><code>source</code></em>,549     <em class="replaceable"><code>pattern</code></em>, <em class="replaceable"><code>replacement</code></em>550     [<span class="optional">, <em class="replaceable"><code>start</code></em>551     [<span class="optional">, <em class="replaceable"><code>N</code></em>552     </span>]</span>]553     [<span class="optional">, <em class="replaceable"><code>flags</code></em> </span>]).554     (Notice that <em class="replaceable"><code>N</code></em> cannot be specified555     unless <em class="replaceable"><code>start</code></em> is,556     but <em class="replaceable"><code>flags</code></em> can be given in any case.)557     The <em class="replaceable"><code>source</code></em> string is returned unchanged if558     there is no match to the <em class="replaceable"><code>pattern</code></em>.  If there is a559     match, the <em class="replaceable"><code>source</code></em> string is returned with the560     <em class="replaceable"><code>replacement</code></em> string substituted for the matching561     substring.  The <em class="replaceable"><code>replacement</code></em> string can contain562     <code class="literal">\</code><em class="replaceable"><code>n</code></em>, where <em class="replaceable"><code>n</code></em> is 1563     through 9, to indicate that the source substring matching the564     <em class="replaceable"><code>n</code></em>'th parenthesized subexpression of the pattern should be565     inserted, and it can contain <code class="literal">\&amp;</code> to indicate that the566     substring matching the entire pattern should be inserted.  Write567     <code class="literal">\\</code> if you need to put a literal backslash in the replacement568     text.569     <em class="replaceable"><code>pattern</code></em> is searched for570     in <em class="replaceable"><code>string</code></em>, normally from the beginning of571     the string, but if the <em class="replaceable"><code>start</code></em> parameter is572     provided then beginning from that character index.573     By default, only the first match of the pattern is replaced.574     If <em class="replaceable"><code>N</code></em> is specified and is greater than zero,575     then the <em class="replaceable"><code>N</code></em>'th match of the pattern576     is replaced.577     If the <code class="literal">g</code> flag is given, or578     if <em class="replaceable"><code>N</code></em> is specified and is zero, then all579     matches at or after the <em class="replaceable"><code>start</code></em> position are580     replaced.  (The <code class="literal">g</code> flag is ignored581     when <em class="replaceable"><code>N</code></em> is specified.)582     The <em class="replaceable"><code>flags</code></em> parameter is an optional text583     string containing zero or more single-letter flags that change the584     function's behavior.  Supported flags (though585     not <code class="literal">g</code>) are586     described in <a class="xref" href="functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE" title="Table 9.24. ARE Embedded-Option Letters">Table 9.24</a>.587    </p><p>588    Some examples:589</p><pre class="programlisting">590regexp_replace('foobarbaz', 'b..', 'X')591                                   <em class="lineannotation"><span class="lineannotation">fooXbaz</span></em>592regexp_replace('foobarbaz', 'b..', 'X', 'g')593                                   <em class="lineannotation"><span class="lineannotation">fooXX</span></em>594regexp_replace('foobarbaz', 'b(..)', 'X\1Y', 'g')595                                   <em class="lineannotation"><span class="lineannotation">fooXarYXazY</span></em>596regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 0, 'i')597                                   <em class="lineannotation"><span class="lineannotation">X PXstgrXSQL fXnctXXn</span></em>598regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 3, 'i')599                                   <em class="lineannotation"><span class="lineannotation">A PostgrXSQL function</span></em>600</pre><p>601   </p><p>602     The <code class="function">regexp_split_to_table</code> function splits a string using a POSIX603     regular expression pattern as a delimiter.  It has the syntax604     <code class="function">regexp_split_to_table</code>(<em class="replaceable"><code>string</code></em>, <em class="replaceable"><code>pattern</code></em>605     [<span class="optional">, <em class="replaceable"><code>flags</code></em> </span>]).606     If there is no match to the <em class="replaceable"><code>pattern</code></em>, the function returns the607     <em class="replaceable"><code>string</code></em>.  If there is at least one match, for each match it returns608     the text from the end of the last match (or the beginning of the string)609     to the beginning of the match.  When there are no more matches, it610     returns the text from the end of the last match to the end of the string.611     The <em class="replaceable"><code>flags</code></em> parameter is an optional text string containing612     zero or more single-letter flags that change the function's behavior.613     <code class="function">regexp_split_to_table</code> supports the flags described in614     <a class="xref" href="functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE" title="Table 9.24. ARE Embedded-Option Letters">Table 9.24</a>.615    </p><p>616     The <code class="function">regexp_split_to_array</code> function behaves the same as617     <code class="function">regexp_split_to_table</code>, except that <code class="function">regexp_split_to_array</code>618     returns its result as an array of <code class="type">text</code>.  It has the syntax619     <code class="function">regexp_split_to_array</code>(<em class="replaceable"><code>string</code></em>, <em class="replaceable"><code>pattern</code></em>620     [<span class="optional">, <em class="replaceable"><code>flags</code></em> </span>]).621     The parameters are the same as for <code class="function">regexp_split_to_table</code>.622    </p><p>623    Some examples:624</p><pre class="programlisting">625SELECT foo FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', '\s+') AS foo;626  foo627-------628 the629 quick630 brown631 fox632 jumps633 over634 the635 lazy636 dog637(9 rows)638 639SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', '\s+');640              regexp_split_to_array641-----------------------------------------------642 {the,quick,brown,fox,jumps,over,the,lazy,dog}643(1 row)644 645SELECT foo FROM regexp_split_to_table('the quick brown fox', '\s*') AS foo;646 foo647-----648 t649 h650 e651 q652 u653 i654 c655 k656 b657 r658 o659 w660 n661 f662 o663 x664(16 rows)665</pre><p>666   </p><p>667    As the last example demonstrates, the regexp split functions ignore668    zero-length matches that occur at the start or end of the string669    or immediately after a previous match.  This is contrary to the strict670    definition of regexp matching that is implemented by671    the other regexp functions, but is usually the most convenient behavior672    in practice.  Other software systems such as Perl use similar definitions.673   </p><p>674     The <code class="function">regexp_substr</code> function returns the substring675     that matches a POSIX regular expression pattern,676     or <code class="literal">NULL</code> if there is no match.  It has the syntax677     <code class="function">regexp_substr</code>(<em class="replaceable"><code>string</code></em>,678     <em class="replaceable"><code>pattern</code></em>679     [<span class="optional">, <em class="replaceable"><code>start</code></em>680     [<span class="optional">, <em class="replaceable"><code>N</code></em>681     [<span class="optional">, <em class="replaceable"><code>flags</code></em>682     [<span class="optional">, <em class="replaceable"><code>subexpr</code></em>683     </span>]</span>]</span>]</span>]).684     <em class="replaceable"><code>pattern</code></em> is searched for685     in <em class="replaceable"><code>string</code></em>, normally from the beginning of686     the string, but if the <em class="replaceable"><code>start</code></em> parameter is687     provided then beginning from that character index.688     If <em class="replaceable"><code>N</code></em> is specified689     then the <em class="replaceable"><code>N</code></em>'th match of the pattern690     is returned, otherwise the first match is returned.691     The <em class="replaceable"><code>flags</code></em> parameter is an optional text692     string containing zero or more single-letter flags that change the693     function's behavior.  Supported flags are described694     in <a class="xref" href="functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE" title="Table 9.24. ARE Embedded-Option Letters">Table 9.24</a>.695     For a pattern containing parenthesized696     subexpressions, <em class="replaceable"><code>subexpr</code></em> is an integer697     indicating which subexpression is of interest: the result is the698     substring matching that subexpression.699     Subexpressions are numbered in the order of their leading parentheses.700     When <em class="replaceable"><code>subexpr</code></em> is omitted or zero, the result701     is the whole match regardless of parenthesized subexpressions.702    </p><p>703     Some examples:704</p><pre class="programlisting">705regexp_substr('number of your street, town zip, FR', '[^,]+', 1, 2)706                                   <em class="lineannotation"><span class="lineannotation"> town zip</span></em>707regexp_substr('ABCDEFGHI', '(c..)(...)', 1, 1, 'i', 2)708                                   <em class="lineannotation"><span class="lineannotation">FGH</span></em>709</pre><p>710    </p><div class="sect3" id="POSIX-SYNTAX-DETAILS"><div class="titlepage"><div><div><h4 class="title">9.7.3.1. Regular Expression Details <a href="#POSIX-SYNTAX-DETAILS" class="id_link">#</a></h4></div></div></div><p>711    <span class="productname">PostgreSQL</span>'s regular expressions are implemented712    using a software package written by Henry Spencer.  Much of713    the description of regular expressions below is copied verbatim from his714    manual.715   </p><p>716    Regular expressions (<acronym class="acronym">RE</acronym>s), as defined in717    <acronym class="acronym">POSIX</acronym> 1003.2, come in two forms:718    <em class="firstterm">extended</em> <acronym class="acronym">RE</acronym>s or <acronym class="acronym">ERE</acronym>s719    (roughly those of <code class="command">egrep</code>), and720    <em class="firstterm">basic</em> <acronym class="acronym">RE</acronym>s or <acronym class="acronym">BRE</acronym>s721    (roughly those of <code class="command">ed</code>).722    <span class="productname">PostgreSQL</span> supports both forms, and723    also implements some extensions724    that are not in the POSIX standard, but have become widely used725    due to their availability in programming languages such as Perl and Tcl.726    <acronym class="acronym">RE</acronym>s using these non-POSIX extensions are called727    <em class="firstterm">advanced</em> <acronym class="acronym">RE</acronym>s or <acronym class="acronym">ARE</acronym>s728    in this documentation.  AREs are almost an exact superset of EREs,729    but BREs have several notational incompatibilities (as well as being730    much more limited).731    We first describe the ARE and ERE forms, noting features that apply732    only to AREs, and then describe how BREs differ.733   </p><div class="note"><h3 class="title">Note</h3><p>734     <span class="productname">PostgreSQL</span> always initially presumes that a regular735     expression follows the ARE rules.  However, the more limited ERE or736     BRE rules can be chosen by prepending an <em class="firstterm">embedded option</em>737     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>.738     This can be useful for compatibility with applications that expect739     exactly the <acronym class="acronym">POSIX</acronym> 1003.2 rules.740    </p></div><p>741    A regular expression is defined as one or more742    <em class="firstterm">branches</em>, separated by743    <code class="literal">|</code>.  It matches anything that matches one of the744    branches.745   </p><p>746    A branch is zero or more <em class="firstterm">quantified atoms</em> or747    <em class="firstterm">constraints</em>, concatenated.748    It matches a match for the first, followed by a match for the second, etc.;749    an empty branch matches the empty string.750   </p><p>751    A quantified atom is an <em class="firstterm">atom</em> possibly followed752    by a single <em class="firstterm">quantifier</em>.753    Without a quantifier, it matches a match for the atom.754    With a quantifier, it can match some number of matches of the atom.755    An <em class="firstterm">atom</em> can be any of the possibilities756    shown in <a class="xref" href="functions-matching.html#POSIX-ATOMS-TABLE" title="Table 9.17. Regular Expression Atoms">Table 9.17</a>.757    The possible quantifiers and their meanings are shown in758    <a class="xref" href="functions-matching.html#POSIX-QUANTIFIERS-TABLE" title="Table 9.18. Regular Expression Quantifiers">Table 9.18</a>.759   </p><p>760    A <em class="firstterm">constraint</em> matches an empty string, but matches only when761    specific conditions are met.  A constraint can be used where an atom762    could be used, except it cannot be followed by a quantifier.763    The simple constraints are shown in764    <a class="xref" href="functions-matching.html#POSIX-CONSTRAINTS-TABLE" title="Table 9.19. Regular Expression Constraints">Table 9.19</a>;765    some more constraints are described later.766   </p><div class="table" id="POSIX-ATOMS-TABLE"><p class="title"><strong>Table 9.17. 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)767       matches a match for768       <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 reporting769       (a <span class="quote">“<span class="quote">non-capturing</span>”</span> set of parentheses)770       (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>,771       matching any one of the <em class="replaceable"><code>chars</code></em> (see772       <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)773       matches that character taken as an ordinary character,774       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 alphanumeric775       (possibly followed by other characters)776       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>777       (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,778       matches the left-brace character <code class="literal">{</code>;779       when followed by a digit, it is the beginning of a780       <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 other781       significance, matches that character </td></tr></tbody></table></div></div><br class="table-break" /><p>782    An RE cannot end with a backslash (<code class="literal">\</code>).783   </p><div class="note"><h3 class="title">Note</h3><p>784     If you have <a class="xref" href="runtime-config-compatible.html#GUC-STANDARD-CONFORMING-STRINGS">standard_conforming_strings</a> turned off,785     any backslashes you write in literal string constants will need to be786     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.787    </p></div><div class="table" id="POSIX-QUANTIFIERS-TABLE"><p class="title"><strong>Table 9.18. 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>788       <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>789       (inclusive) matches of the atom; <em class="replaceable"><code>m</code></em> cannot exceed790       <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>791       <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>792    The forms using <code class="literal">{</code><em class="replaceable"><code>...</code></em><code class="literal">}</code>793    are known as <em class="firstterm">bounds</em>.794    The numbers <em class="replaceable"><code>m</code></em> and <em class="replaceable"><code>n</code></em> within a bound are795    unsigned decimal integers with permissible values from 0 to 255 inclusive.796   </p><p>797     <em class="firstterm">Non-greedy</em> quantifiers (available in AREs only) match the798     same possibilities as their corresponding normal (<em class="firstterm">greedy</em>)799     counterparts, but prefer the smallest number rather than the largest800     number of matches.801     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.802   </p><div class="note"><h3 class="title">Note</h3><p>803     A quantifier cannot immediately follow another quantifier, e.g.,804     <code class="literal">**</code> is invalid.805     A quantifier cannot806     begin an expression or subexpression or follow807     <code class="literal">^</code> or <code class="literal">|</code>.808    </p></div><div class="table" id="POSIX-CONSTRAINTS-TABLE"><p class="title"><strong>Table 9.19. 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 point809       where a substring matching <em class="replaceable"><code>re</code></em> begins810       (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 point811       where no substring matching <em class="replaceable"><code>re</code></em> begins812       (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 point813       where a substring matching <em class="replaceable"><code>re</code></em> ends814       (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 point815       where no substring matching <em class="replaceable"><code>re</code></em> ends816       (AREs only) </td></tr></tbody></table></div></div><br class="table-break" /><p>817    Lookahead and lookbehind constraints cannot contain <em class="firstterm">back818    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>),819    and all parentheses within them are considered non-capturing.820   </p></div><div class="sect3" id="POSIX-BRACKET-EXPRESSIONS"><div class="titlepage"><div><div><h4 class="title">9.7.3.2. Bracket Expressions <a href="#POSIX-BRACKET-EXPRESSIONS" class="id_link">#</a></h4></div></div></div><p>821    A <em class="firstterm">bracket expression</em> is a list of822    characters enclosed in <code class="literal">[]</code>.  It normally matches823    any single character from the list (but see below).  If the list824    begins with <code class="literal">^</code>, it matches any single character825    <span class="emphasis"><em>not</em></span> from the rest of the list.826    If two characters827    in the list are separated by <code class="literal">-</code>, this is828    shorthand for the full range of characters between those two829    (inclusive) in the collating sequence,830    e.g., <code class="literal">[0-9]</code> in <acronym class="acronym">ASCII</acronym> matches831    any decimal digit.  It is illegal for two ranges to share an832    endpoint, e.g.,  <code class="literal">a-c-e</code>.  Ranges are very833    collating-sequence-dependent, so portable programs should avoid834    relying on them.835   </p><p>836    To include a literal <code class="literal">]</code> in the list, make it the837    first character (after <code class="literal">^</code>, if that is used).  To838    include a literal <code class="literal">-</code>, make it the first or last839    character, or the second endpoint of a range.  To use a literal840    <code class="literal">-</code> as the first endpoint of a range, enclose it841    in <code class="literal">[.</code> and <code class="literal">.]</code> to make it a842    collating element (see below).  With the exception of these characters,843    some combinations using <code class="literal">[</code>844    (see next paragraphs), and escapes (AREs only), all other special845    characters lose their special significance within a bracket expression.846    In particular, <code class="literal">\</code> is not special when following847    ERE or BRE rules, though it is special (as introducing an escape)848    in AREs.849   </p><p>850    Within a bracket expression, a collating element (a character, a851    multiple-character sequence that collates as if it were a single852    character, or a collating-sequence name for either) enclosed in853    <code class="literal">[.</code> and <code class="literal">.]</code> stands for the854    sequence of characters of that collating element.  The sequence is855    treated as a single element of the bracket expression's list.  This856    allows a bracket857    expression containing a multiple-character collating element to858    match more than one character, e.g., if the collating sequence859    includes a <code class="literal">ch</code> collating element, then the RE860    <code class="literal">[[.ch.]]*c</code> matches the first five characters of861    <code class="literal">chchcc</code>.862   </p><div class="note"><h3 class="title">Note</h3><p>863     <span class="productname">PostgreSQL</span> currently does not support multi-character collating864     elements. This information describes possible future behavior.865    </p></div><p>866    Within a bracket expression, a collating element enclosed in867    <code class="literal">[=</code> and <code class="literal">=]</code> is an <em class="firstterm">equivalence868    class</em>, standing for the sequences of characters of all collating869    elements equivalent to that one, including itself.  (If there are870    no other equivalent collating elements, the treatment is as if the871    enclosing delimiters were <code class="literal">[.</code> and872    <code class="literal">.]</code>.)  For example, if <code class="literal">o</code> and873    <code class="literal">^</code> are the members of an equivalence class, then874    <code class="literal">[[=o=]]</code>, <code class="literal">[[=^=]]</code>, and875    <code class="literal">[o^]</code> are all synonymous.  An equivalence class876    cannot be an endpoint of a range.877   </p><p>878    Within a bracket expression, the name of a character class879    enclosed in <code class="literal">[:</code> and <code class="literal">:]</code> stands880    for the list of all characters belonging to that class.  A character881    class cannot be used as an endpoint of a range.882    The <acronym class="acronym">POSIX</acronym> standard defines these character class883    names:884    <code class="literal">alnum</code> (letters and numeric digits),885    <code class="literal">alpha</code> (letters),886    <code class="literal">blank</code> (space and tab),887    <code class="literal">cntrl</code> (control characters),888    <code class="literal">digit</code> (numeric digits),889    <code class="literal">graph</code> (printable characters except space),890    <code class="literal">lower</code> (lower-case letters),891    <code class="literal">print</code> (printable characters including space),892    <code class="literal">punct</code> (punctuation),893    <code class="literal">space</code> (any white space),894    <code class="literal">upper</code> (upper-case letters),895    and <code class="literal">xdigit</code> (hexadecimal digits).896    The behavior of these standard character classes is generally897    consistent across platforms for characters in the 7-bit ASCII set.898    Whether a given non-ASCII character is considered to belong to one899    of these classes depends on the <em class="firstterm">collation</em>900    that is used for the regular-expression function or operator901    (see <a class="xref" href="collation.html" title="24.2. Collation Support">Section 24.2</a>), or by default on the902    database's <code class="envar">LC_CTYPE</code> locale setting (see903    <a class="xref" href="locale.html" title="24.1. Locale Support">Section 24.1</a>).  The classification of non-ASCII904    characters can vary across platforms even in similarly-named905    locales.  (But the <code class="literal">C</code> locale never considers any906    non-ASCII characters to belong to any of these classes.)907    In addition to these standard character908    classes, <span class="productname">PostgreSQL</span> defines909    the <code class="literal">word</code> character class, which is the same as910    <code class="literal">alnum</code> plus the underscore (<code class="literal">_</code>)911    character, and912    the <code class="literal">ascii</code> character class, which contains exactly913    the 7-bit ASCII set.914   </p><p>915    There are two special cases of bracket expressions:  the bracket916    expressions <code class="literal">[[:&lt;:]]</code> and917    <code class="literal">[[:&gt;:]]</code> are constraints,918    matching empty strings at the beginning919    and end of a word respectively.  A word is defined as a sequence920    of word characters that is neither preceded nor followed by word921    characters.  A word character is any character belonging to the922    <code class="literal">word</code> character class, that is, any letter, digit,923    or underscore.  This is an extension, compatible with but not924    specified by <acronym class="acronym">POSIX</acronym> 1003.2, and should be used with925    caution in software intended to be portable to other systems.926    The constraint escapes described below are usually preferable; they927    are no more standard, but are easier to type.928   </p></div><div class="sect3" id="POSIX-ESCAPE-SEQUENCES"><div class="titlepage"><div><div><h4 class="title">9.7.3.3. Regular Expression Escapes <a href="#POSIX-ESCAPE-SEQUENCES" class="id_link">#</a></h4></div></div></div><p>929    <em class="firstterm">Escapes</em> are special sequences beginning with <code class="literal">\</code>930    followed by an alphanumeric character. Escapes come in several varieties:931    character entry, class shorthands, constraint escapes, and back references.932    A <code class="literal">\</code> followed by an alphanumeric character but not constituting933    a valid escape is illegal in AREs.934    In EREs, there are no escapes: outside a bracket expression,935    a <code class="literal">\</code> followed by an alphanumeric character merely stands for936    that character as an ordinary character, and inside a bracket expression,937    <code class="literal">\</code> is an ordinary character.938    (The latter is the one actual incompatibility between EREs and AREs.)939   </p><p>940    <em class="firstterm">Character-entry escapes</em> exist to make it easier to specify941    non-printing and other inconvenient characters in REs.  They are942    shown in <a class="xref" href="functions-matching.html#POSIX-CHARACTER-ENTRY-ESCAPES-TABLE" title="Table 9.20. Regular Expression Character-Entry Escapes">Table 9.20</a>.943   </p><p>944    <em class="firstterm">Class-shorthand escapes</em> provide shorthands for certain945    commonly-used character classes.  They are946    shown in <a class="xref" href="functions-matching.html#POSIX-CLASS-SHORTHAND-ESCAPES-TABLE" title="Table 9.21. Regular Expression Class-Shorthand Escapes">Table 9.21</a>.947   </p><p>948    A <em class="firstterm">constraint escape</em> is a constraint,949    matching the empty string if specific conditions are met,950    written as an escape.  They are951    shown in <a class="xref" href="functions-matching.html#POSIX-CONSTRAINT-ESCAPES-TABLE" title="Table 9.22. Regular Expression Constraint Escapes">Table 9.22</a>.952   </p><p>953    A <em class="firstterm">back reference</em> (<code class="literal">\</code><em class="replaceable"><code>n</code></em>) matches the954    same string matched by the previous parenthesized subexpression specified955    by the number <em class="replaceable"><code>n</code></em>956    (see <a class="xref" href="functions-matching.html#POSIX-CONSTRAINT-BACKREF-TABLE" title="Table 9.23. Regular Expression Back References">Table 9.23</a>).  For example,957    <code class="literal">([bc])\1</code> matches <code class="literal">bb</code> or <code class="literal">cc</code>958    but not <code class="literal">bc</code> or <code class="literal">cb</code>.959    The subexpression must entirely precede the back reference in the RE.960    Subexpressions are numbered in the order of their leading parentheses.961    Non-capturing parentheses do not define subexpressions.962    The back reference considers only the string characters matched by the963    referenced subexpression, not any constraints contained in it.  For964    example, <code class="literal">(^\d)\1</code> will match <code class="literal">22</code>.965   </p><div class="table" id="POSIX-CHARACTER-ENTRY-ESCAPES-TABLE"><p class="title"><strong>Table 9.20. 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 backslash966       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 whose967       low-order 5 bits are the same as those of968       <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 name969       is <code class="literal">ESC</code>,970       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)971       the character whose hexadecimal value is972       <code class="literal">0x</code><em class="replaceable"><code>wxyz</code></em>973       </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 hexadecimal974       digits)975       the character whose hexadecimal value is976       <code class="literal">0x</code><em class="replaceable"><code>stuvwxyz</code></em>977       </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 hexadecimal978       digits)979       the character whose hexadecimal value is980       <code class="literal">0x</code><em class="replaceable"><code>hhh</code></em>981       (a single character no matter how many hexadecimal digits are used)982       </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,983       and is not a <em class="firstterm">back reference</em>)984       the character whose octal value is985       <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,986       and is not a <em class="firstterm">back reference</em>)987       the character whose octal value is988       <code class="literal">0</code><em class="replaceable"><code>xyz</code></em> </td></tr></tbody></table></div></div><br class="table-break" /><p>989    Hexadecimal digits are <code class="literal">0</code>-<code class="literal">9</code>,990    <code class="literal">a</code>-<code class="literal">f</code>, and <code class="literal">A</code>-<code class="literal">F</code>.991    Octal digits are <code class="literal">0</code>-<code class="literal">7</code>.992   </p><p>993    Numeric character-entry escapes specifying values outside the ASCII range994    (0–127) have meanings dependent on the database encoding.  When the995    encoding is UTF-8, escape values are equivalent to Unicode code points,996    for example <code class="literal">\u1234</code> means the character <code class="literal">U+1234</code>.997    For other multibyte encodings, character-entry escapes usually just998    specify the concatenation of the byte values for the character.  If the999    escape value does not correspond to any legal character in the database1000    encoding, no error will be raised, but it will never match any data.1001   </p><p>1002    The character-entry escapes are always taken as ordinary characters.1003    For example, <code class="literal">\135</code> is <code class="literal">]</code> in ASCII, but1004    <code class="literal">\135</code> does not terminate a bracket expression.1005   </p><div class="table" id="POSIX-CLASS-SHORTHAND-ESCAPES-TABLE"><p class="title"><strong>Table 9.21. 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> matches any digit, like1006        <code class="literal">[[:digit:]]</code> </td></tr><tr><td> <code class="literal">\s</code> </td><td> matches any whitespace character, like1007        <code class="literal">[[:space:]]</code> </td></tr><tr><td> <code class="literal">\w</code> </td><td> matches any word character, like1008        <code class="literal">[[:word:]]</code> </td></tr><tr><td> <code class="literal">\D</code> </td><td> matches any non-digit, like1009        <code class="literal">[^[:digit:]]</code> </td></tr><tr><td> <code class="literal">\S</code> </td><td> matches any non-whitespace character, like1010        <code class="literal">[^[:space:]]</code> </td></tr><tr><td> <code class="literal">\W</code> </td><td> matches any non-word character, like1011        <code class="literal">[^[:word:]]</code> </td></tr></tbody></table></div></div><br class="table-break" /><p>1012    The class-shorthand escapes also work within bracket expressions,1013    although the definitions shown above are not quite syntactically1014    valid in that context.1015    For example, <code class="literal">[a-c\d]</code> is equivalent to1016    <code class="literal">[a-c[:digit:]]</code>.1017   </p><div class="table" id="POSIX-CONSTRAINT-ESCAPES-TABLE"><p class="title"><strong>Table 9.22. 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 string1018       (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 from1019       <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 a1020       word </td></tr><tr><td> <code class="literal">\Z</code> </td><td> matches only at the end of the string1021       (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 from1022       <code class="literal">$</code>) </td></tr></tbody></table></div></div><br class="table-break" /><p>1023    A word is defined as in the specification of1024    <code class="literal">[[:&lt;:]]</code> and <code class="literal">[[:&gt;:]]</code> above.1025    Constraint escapes are illegal within bracket expressions.1026   </p><div class="table" id="POSIX-CONSTRAINT-BACKREF-TABLE"><p class="title"><strong>Table 9.23. 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)1027       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, and1028       <em class="replaceable"><code>nn</code></em> is some more digits, and the decimal value1029       <em class="replaceable"><code>mnn</code></em> is not greater than the number of closing capturing1030       parentheses seen so far)1031       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>1032     There is an inherent ambiguity between octal character-entry1033     escapes and back references, which is resolved by the following heuristics,1034     as hinted at above.1035     A leading zero always indicates an octal escape.1036     A single non-zero digit, not followed by another digit,1037     is always taken as a back reference.1038     A multi-digit sequence not starting with a zero is taken as a back1039     reference if it comes after a suitable subexpression1040     (i.e., the number is in the legal range for a back reference),1041     and otherwise is taken as octal.1042    </p></div></div><div class="sect3" id="POSIX-METASYNTAX"><div class="titlepage"><div><div><h4 class="title">9.7.3.4. Regular Expression Metasyntax <a href="#POSIX-METASYNTAX" class="id_link">#</a></h4></div></div></div><p>1043    In addition to the main syntax described above, there are some special1044    forms and miscellaneous syntactic facilities available.1045   </p><p>1046    An RE can begin with one of two special <em class="firstterm">director</em> prefixes.1047    If an RE begins with <code class="literal">***:</code>,1048    the rest of the RE is taken as an ARE.  (This normally has no effect in1049    <span class="productname">PostgreSQL</span>, since REs are assumed to be AREs;1050    but it does have an effect if ERE or BRE mode had been specified by1051    the <em class="replaceable"><code>flags</code></em> parameter to a regex function.)1052    If an RE begins with <code class="literal">***=</code>,1053    the rest of the RE is taken to be a literal string,1054    with all characters considered ordinary characters.1055   </p><p>1056    An ARE can begin with <em class="firstterm">embedded options</em>:1057    a sequence <code class="literal">(?</code><em class="replaceable"><code>xyz</code></em><code class="literal">)</code>1058    (where <em class="replaceable"><code>xyz</code></em> is one or more alphabetic characters)1059    specifies options affecting the rest of the RE.1060    These options override any previously determined options —1061    in particular, they can override the case-sensitivity behavior implied by1062    a regex operator, or the <em class="replaceable"><code>flags</code></em> parameter to a regex1063    function.1064    The available option letters are1065    shown in <a class="xref" href="functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE" title="Table 9.24. ARE Embedded-Option Letters">Table 9.24</a>.1066    Note that these same option letters are used in the <em class="replaceable"><code>flags</code></em>1067    parameters of regex functions.1068   </p><div class="table" id="POSIX-EMBEDDED-OPTIONS-TABLE"><p class="title"><strong>Table 9.24. 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 (see1069       <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 (see1070       <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 (see1071       <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 ordinary1072       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>) matching1073       (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>1074    Embedded options take effect at the <code class="literal">)</code> terminating the sequence.1075    They can appear only at the start of an ARE (after the1076    <code class="literal">***:</code> director if any).1077   </p><p>1078    In addition to the usual (<em class="firstterm">tight</em>) RE syntax, in which all1079    characters are significant, there is an <em class="firstterm">expanded</em> syntax,1080    available by specifying the embedded <code class="literal">x</code> option.1081    In the expanded syntax,1082    white-space characters in the RE are ignored, as are1083    all characters between a <code class="literal">#</code>1084    and the following newline (or the end of the RE).  This1085    permits paragraphing and commenting a complex RE.1086    There are three exceptions to that basic rule:1087 1088    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>1089       a white-space character or <code class="literal">#</code> preceded by <code class="literal">\</code> is1090       retained1091      </p></li><li class="listitem"><p>1092       white space or <code class="literal">#</code> within a bracket expression is retained1093      </p></li><li class="listitem"><p>1094       white space and comments cannot appear within multi-character symbols,1095       such as <code class="literal">(?:</code>1096      </p></li></ul></div><p>1097 1098    For this purpose, white-space characters are blank, tab, newline, and1099    any character that belongs to the <em class="replaceable"><code>space</code></em> character class.1100   </p><p>1101    Finally, in an ARE, outside bracket expressions, the sequence1102    <code class="literal">(?#</code><em class="replaceable"><code>ttt</code></em><code class="literal">)</code>1103    (where <em class="replaceable"><code>ttt</code></em> is any text not containing a <code class="literal">)</code>)1104    is a comment, completely ignored.1105    Again, this is not allowed between the characters of1106    multi-character symbols, like <code class="literal">(?:</code>.1107    Such comments are more a historical artifact than a useful facility,1108    and their use is deprecated; use the expanded syntax instead.1109   </p><p>1110    <span class="emphasis"><em>None</em></span> of these metasyntax extensions is available if1111    an initial <code class="literal">***=</code> director1112    has specified that the user's input be treated as a literal string1113    rather than as an RE.1114   </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 <a href="#POSIX-MATCHING-RULES" class="id_link">#</a></h4></div></div></div><p>1115    In the event that an RE could match more than one substring of a given1116    string, the RE matches the one starting earliest in the string.1117    If the RE could match more than one substring starting at that point,1118    either the longest possible match or the shortest possible match will1119    be taken, depending on whether the RE is <em class="firstterm">greedy</em> or1120    <em class="firstterm">non-greedy</em>.1121   </p><p>1122    Whether an RE is greedy or not is determined by the following rules:1123    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>1124       Most atoms, and all constraints, have no greediness attribute (because1125       they cannot match variable amounts of text anyway).1126      </p></li><li class="listitem"><p>1127       Adding parentheses around an RE does not change its greediness.1128      </p></li><li class="listitem"><p>1129       A quantified atom with a fixed-repetition quantifier1130       (<code class="literal">{</code><em class="replaceable"><code>m</code></em><code class="literal">}</code>1131       or1132       <code class="literal">{</code><em class="replaceable"><code>m</code></em><code class="literal">}?</code>)1133       has the same greediness (possibly none) as the atom itself.1134      </p></li><li class="listitem"><p>1135       A quantified atom with other normal quantifiers (including1136       <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>1137       with <em class="replaceable"><code>m</code></em> equal to <em class="replaceable"><code>n</code></em>)1138       is greedy (prefers longest match).1139      </p></li><li class="listitem"><p>1140       A quantified atom with a non-greedy quantifier (including1141       <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>1142       with <em class="replaceable"><code>m</code></em> equal to <em class="replaceable"><code>n</code></em>)1143       is non-greedy (prefers shortest match).1144      </p></li><li class="listitem"><p>1145       A branch — that is, an RE that has no top-level1146       <code class="literal">|</code> operator — has the same greediness as the first1147       quantified atom in it that has a greediness attribute.1148      </p></li><li class="listitem"><p>1149       An RE consisting of two or more branches connected by the1150       <code class="literal">|</code> operator is always greedy.1151      </p></li></ul></div><p>1152   </p><p>1153    The above rules associate greediness attributes not only with individual1154    quantified atoms, but with branches and entire REs that contain quantified1155    atoms.  What that means is that the matching is done in such a way that1156    the branch, or whole RE, matches the longest or shortest possible1157    substring <span class="emphasis"><em>as a whole</em></span>.  Once the length of the entire match1158    is determined, the part of it that matches any particular subexpression1159    is determined on the basis of the greediness attribute of that1160    subexpression, with subexpressions starting earlier in the RE taking1161    priority over ones starting later.1162   </p><p>1163    An example of what this means:1164</p><pre class="screen">1165SELECT SUBSTRING('XY1234Z', 'Y*([0-9]{1,3})');1166<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">123</code>1167SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');1168<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">1</code>1169</pre><p>1170    In the first case, the RE as a whole is greedy because <code class="literal">Y*</code>1171    is greedy.  It can match beginning at the <code class="literal">Y</code>, and it matches1172    the longest possible string starting there, i.e., <code class="literal">Y123</code>.1173    The output is the parenthesized part of that, or <code class="literal">123</code>.1174    In the second case, the RE as a whole is non-greedy because <code class="literal">Y*?</code>1175    is non-greedy.  It can match beginning at the <code class="literal">Y</code>, and it matches1176    the shortest possible string starting there, i.e., <code class="literal">Y1</code>.1177    The subexpression <code class="literal">[0-9]{1,3}</code> is greedy but it cannot change1178    the decision as to the overall match length; so it is forced to match1179    just <code class="literal">1</code>.1180   </p><p>1181    In short, when an RE contains both greedy and non-greedy subexpressions,1182    the total match length is either as long as possible or as short as1183    possible, according to the attribute assigned to the whole RE.  The1184    attributes assigned to the subexpressions only affect how much of that1185    match they are allowed to <span class="quote">“<span class="quote">eat</span>”</span> relative to each other.1186   </p><p>1187    The quantifiers <code class="literal">{1,1}</code> and <code class="literal">{1,1}?</code>1188    can be used to force greediness or non-greediness, respectively,1189    on a subexpression or a whole RE.1190    This is useful when you need the whole RE to have a greediness attribute1191    different from what's deduced from its elements.  As an example,1192    suppose that we are trying to separate a string containing some digits1193    into the digits and the parts before and after them.  We might try to1194    do that like this:1195</p><pre class="screen">1196SELECT regexp_match('abc01234xyz', '(.*)(\d+)(.*)');1197<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">{abc0123,4,xyz}</code>1198</pre><p>1199    That didn't work: the first <code class="literal">.*</code> is greedy so1200    it <span class="quote">“<span class="quote">eats</span>”</span> as much as it can, leaving the <code class="literal">\d+</code> to

Showing the first 1,200 of 1415 lines. Download the file for the rest.

codekingpro/portable-devtools · Team Ai