Home • Explore • Blog • CI
☰



postgresql
73th chunk of `doc/src/sgml/func.sgml`
db3ac8ee5deff38268ad094e8696b45e43e41596c102d4670000000100000fac
 portion
     of the text that matched the first parenthesized subexpression (the
     one whose left parenthesis comes first) is
     returned.  You can put parentheses around the whole expression
     if you want to use parentheses within it without triggering this
     exception.  If you need parentheses in the pattern before the
     subexpression you want to extract, see the non-capturing parentheses
     described below.
    </para>

   <para>
    Some examples:
<programlisting>
substring('foobar' from 'o.b')     <lineannotation>oob</lineannotation>
substring('foobar' from 'o(.)b')   <lineannotation>o</lineannotation>
</programlisting>
   </para>

    <para>
     The <function>regexp_count</function> function counts the number of
     places where a POSIX regular expression pattern matches a string.
     It has the syntax
     <function>regexp_count</function>(<replaceable>string</replaceable>,
     <replaceable>pattern</replaceable>
     <optional>, <replaceable>start</replaceable>
     <optional>, <replaceable>flags</replaceable>
     </optional></optional>).
     <replaceable>pattern</replaceable> is searched for
     in <replaceable>string</replaceable>, normally from the beginning of
     the string, but if the <replaceable>start</replaceable> parameter is
     provided then beginning from that character index.
     The <replaceable>flags</replaceable> parameter is an optional text
     string containing zero or more single-letter flags that change the
     function's behavior.  For example, including <literal>i</literal> in
     <replaceable>flags</replaceable> specifies case-insensitive matching.
     Supported flags are described in
     <xref linkend="posix-embedded-options-table"/>.
    </para>

    <para>
     Some examples:
<programlisting>
regexp_count('ABCABCAXYaxy', 'A.')          <lineannotation>3</lineannotation>
regexp_count('ABCABCAXYaxy', 'A.', 1, 'i')  <lineannotation>4</lineannotation>
</programlisting>
    </para>

    <para>
     The <function>regexp_instr</function> function returns the starting or
     ending position of the <replaceable>N</replaceable>'th match of a
     POSIX regular expression pattern to a string, or zero if there is no
     such match.  It has the syntax
     <function>regexp_instr</function>(<replaceable>string</replaceable>,
     <replaceable>pattern</replaceable>
     <optional>, <replaceable>start</replaceable>
     <optional>, <replaceable>N</replaceable>
     <optional>, <replaceable>endoption</replaceable>
     <optional>, <replaceable>flags</replaceable>
     <optional>, <replaceable>subexpr</replaceable>
     </optional></optional></optional></optional></optional>).
     <replaceable>pattern</replaceable> is searched for
     in <replaceable>string</replaceable>, normally from the beginning of
     the string, but if the <replaceable>start</replaceable> parameter is
     provided then beginning from that character index.
     If <replaceable>N</replaceable> is specified
     then the <replaceable>N</replaceable>'th match of the pattern
     is located, otherwise the first match is located.
     If the <replaceable>endoption</replaceable> parameter is omitted or
     specified as zero, the function returns the position of the first
     character of the match.  Otherwise, <replaceable>endoption</replaceable>
     must be one, and the function returns the position of the character
     following the match.
     The <replaceable>flags</replaceable> parameter is an optional text
     string containing zero or more single-letter flags that change the
     function's behavior.  Supported flags are described
     in <xref linkend="posix-embedded-options-table"/>.
     For a pattern containing parenthesized
     subexpressions, <replaceable>subexpr</replaceable> is an integer
     indicating which subexpression is of interest: the result identifies
     the position of the substring matching that subexpression.
     Subexpressions are numbered in the order of their leading parentheses.
Title: regexp_count and regexp_instr Functions for POSIX Regular Expressions
Summary
This section describes the `regexp_count` and `regexp_instr` functions, both of which work with POSIX regular expressions. `regexp_count` counts the number of times a pattern matches a string, with optional start position and flags for case-insensitive matching. `regexp_instr` finds the starting or ending position of the Nth match of a pattern in a string, with options to specify the starting position, which match to find, whether to return the start or end position of the match, flags for modifying behavior, and which subexpression to focus on within a pattern with parenthesized subexpressions.