Oracle’s Regular Expression Support

Oracle’s regular expression support manifests itself in the form of three SQL functions and one predicate that you can use to search and manipulate text in any of Oracle’s supported text datatypes: VARCHAR2, CHAR, NVARCHAR2, NCHAR, CLOB, and NCLOB.


Regular expression support does not extend to LONG, because LONG is supported only for backward compatibility with existing code.

Regular Expression Functions

Following are the four functions you’ll use to work with regular expressions in Oracle:


Determines whether a specific column, variable, or text literal contains text matching a regular expression.


Locates, by character position, an occurrence of text matching a regular expression.


Replaces text matching a regular expression with new text that you specify. Your replacement text can include backreferences to values in the regular expression.


Extracts text matching a regular expression from a character column, variable, or text literal.

Of these, you’ve already seen REGEXP_LIKE in quite a few examples. REGEXP_LIKE is documented in the “Conditions” chapter of the Oracle Database 10g SQL Reference because in SQL it can only be used as a predicate in the WHERE and HAVING clauses of a query or DML statement. In PL/SQL, however, you can use REGEXP_LIKE as you would any other Boolean function:

   x Boolean;
   x := REGEXP_LIKE(
        'Does this string mention Oracle?',

The remaining three ...

Get Oracle Regular Expressions Pocket Reference now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.