Oracle Regular Expressions Pocket Reference

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.

Note

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:

REGEXP_LIKE

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

REGEXP_INSTR

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

REGEXP_REPLACE

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

REGEXP_SUBSTR

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:

DECLARE
   x Boolean;
BEGIN
   x := REGEXP_LIKE(
        'Does this string mention Oracle?',
        'Oracle');
END;
/

The remaining three ...