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.
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:
DECLARE x Boolean; BEGIN x := REGEXP_LIKE( 'Does this string mention Oracle?', 'Oracle'); END; /
The remaining three ...