Home • Explore • Blog • CI
☰



postgresql
96th chunk of `doc/src/sgml/func.sgml`
16b8c859b410c1c6662804351ef1d0048694d11791005c730000000100000fa1
 <listitem><para><literal>TRANSLATE_REGEX</literal></para></listitem>
     </itemizedlist>
     <productname>PostgreSQL</productname> does not currently implement these
     operators and functions.  You can get approximately equivalent
     functionality in each case as shown in <xref
     linkend="functions-regexp-sql-table"/>.  (Various optional clauses on
     both sides have been omitted in this table.)
    </para>

    <table id="functions-regexp-sql-table">
     <title>Regular Expression Functions Equivalencies</title>

     <tgroup cols="2">
      <thead>
       <row>
        <entry>SQL standard</entry>
        <entry><productname>PostgreSQL</productname></entry>
       </row>
      </thead>

      <tbody>
       <row>
        <entry><literal><replaceable>string</replaceable> LIKE_REGEX <replaceable>pattern</replaceable></literal></entry>
        <entry><literal>regexp_like(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>)</literal> or <literal><replaceable>string</replaceable> ~ <replaceable>pattern</replaceable></literal></entry>
       </row>

       <row>
        <entry><literal>OCCURRENCES_REGEX(<replaceable>pattern</replaceable> IN <replaceable>string</replaceable>)</literal></entry>
        <entry><literal>regexp_count(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>)</literal></entry>
       </row>

       <row>
        <entry><literal>POSITION_REGEX(<replaceable>pattern</replaceable> IN <replaceable>string</replaceable>)</literal></entry>
        <entry><literal>regexp_instr(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>)</literal></entry>
       </row>

       <row>
        <entry><literal>SUBSTRING_REGEX(<replaceable>pattern</replaceable> IN <replaceable>string</replaceable>)</literal></entry>
        <entry><literal>regexp_substr(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>)</literal></entry>
       </row>

       <row>
        <entry><literal>TRANSLATE_REGEX(<replaceable>pattern</replaceable> IN <replaceable>string</replaceable> WITH <replaceable>replacement</replaceable>)</literal></entry>
        <entry><literal>regexp_replace(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>, <replaceable>replacement</replaceable>)</literal></entry>
       </row>
      </tbody>
     </tgroup>
    </table>

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

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

     <itemizedlist>
      <listitem>
       <para>
        XQuery character class subtraction is not supported.  An example of
        this feature is using the following to match only English
        consonants: <literal>[a-z-[aeiou]]</literal>.
       </para>
      </listitem>
      <listitem>
       <para>
        XQuery character class shorthands <literal>\c</literal>,
        <literal>\C</literal>, <literal>\i</literal>,
        and <literal>\I</literal> are not supported.
       </para>
      </listitem>
      <listitem>
       <para>
        XQuery character class elements
        using <literal>\p{UnicodeProperty}</literal> or the
        inverse <literal>\P{UnicodeProperty}</literal> are not supported.
       </para>
      </listitem>
      <listitem>
       <para>
        POSIX interprets character classes such as <literal>\w</literal>
        (see <xref linkend="posix-class-shorthand-escapes-table"/>)
        according to the prevailing locale (which you can control by
        attaching a
Title: Regular Expression Function Equivalencies: SQL Standard vs. PostgreSQL and Differences Between POSIX and XQuery Regular Expressions
Summary
This section highlights the discrepancies between SQL standard regular expression functions (LIKE_REGEX, OCCURRENCES_REGEX, POSITION_REGEX, SUBSTRING_REGEX, TRANSLATE_REGEX) and their PostgreSQL equivalents (regexp_like/~, regexp_count, regexp_instr, regexp_substr, regexp_replace). It details how PostgreSQL provides similar functionality to the SQL standard, even though it doesn't directly implement the standard functions. It also notes that regular expression functions similar to those in PostgreSQL are available in other SQL implementations. The section then outlines the key differences between POSIX-based and XQuery regular expressions, including the lack of support for character class subtraction, specific character class shorthands (\c, \C, \i, \I), and Unicode property character class elements (\p{UnicodeProperty}, \P{UnicodeProperty}) in XQuery.