10g REGULAR EXPRESSIONS (REGEXP_LIKE)

Let us have a look at the examples for each of the above mentioned SQL functions and WHERE clause operator

REGEXP_LIKE

Syntax:

REGEXP_LIKE(source_string, pattern [, match_parameter])

source_string - can be column of CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2, and NCLOB but not LONG datatypes

pattern - the regular expression

match_parameter - allows optional parameters such as handling the newline character, retaining multiline formatting, and providing control over case-sensitivity

Example:

The following SQL query searches the ZIP column for a pattern that satisfies the regular expression [^[:digit:]]. It will retrieve those rows in the ZIPCODE table for which the ZIP column values contain any character that is not a numeric digit.

In case if tha pattern is used as '^[[:digit:]]' then it would look for zip code starting with digits.