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.
Expand|Select|Wrap|Line Numbers
- SELECT zip
- FROM zipcode
- WHERE REGEXP_LIKE(zip, '[^[:digit:]]')
- ZIP
- -----
- ab123
- 123xy
- 007ab
- abcxy