<!-- end re_syntax.n man page -->

<sect3 id="posix-vs-xquery">

<title>Differences fromXQuery (<literal>LIKE_REGEX</literal>)</title>

<title>Differences fromSQL Standard and XQuery</title>

<indexterm zone="posix-vs-xquery">

<primary><literal>LIKE_REGEX</literal></primary>

<primary>LIKE_REGEX</primary>

</indexterm>

<indexterm zone="posix-vs-xquery">

<primary>OCCURRENCES_REGEX</primary>

</indexterm>

<indexterm zone="posix-vs-xquery">

<primary>POSITION_REGEX</primary>

</indexterm>

<indexterm zone="posix-vs-xquery">

<primary>SUBSTRING_REGEX</primary>

</indexterm>

<indexterm zone="posix-vs-xquery">

<primary>TRANSLATE_REGEX</primary>

</indexterm>

<indexterm zone="posix-vs-xquery">

<primary>XQuery regular expressions</primary>

</indexterm>

<para>

Since SQL:2008, the SQL standard includes

a <literal>LIKE_REGEX</literal> operator that performs pattern

Since SQL:2008, the SQL standard includes regular expression operators

and functions that performs pattern

matching according to the XQuery regular expression

standard. <productname>PostgreSQL</productname> does not yet

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

the <function>regexp_match()</function> function, since XQuery

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

standard:

<itemizedlist>

<listitem><para><literal>LIKE_REGEX</literal></para></listitem>

<listitem><para><literal>OCCURRENCES_REGEX</literal></para></listitem>

<listitem><para><literal>POSITION_REGEX</literal></para></listitem>

<listitem><para><literal>SUBSTRING_REGEX</literal></para></listitem>

<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>PostgreSQL</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:

F831Full cursor updateNO

F831Full cursor update01Updatable scrollable cursorsNO

F831Full cursor update02Updatable ordered cursorsNO

F841LIKE_REGEX predicateNO

F842OCCURRENCES_REGEX functionNO

F843POSITION_REGEX functionNO

F844SUBSTRING_REGEX functionNO

F845TRANSLATE_REGEX functionNO

F841LIKE_REGEX predicateNOconsider regexp_like()

F842OCCURRENCES_REGEX functionNOconsider regexp_matches()

F843POSITION_REGEX functionNOconsider regexp_instr()

F844SUBSTRING_REGEX functionNOconsider regexp_substr()

F845TRANSLATE_REGEX functionNOconsider regexp_replace()

F846Octet support in regular expression operatorsNO

F847Nonconstant regular expressionsNO

F850Top-level <order by clause> in <query expression>YES