Name
regexp_like — Allows a like comparison using regular-expression.
Synopsis
integer
regexp_like
(
|
in source_string any , |
| in pattern any , | |
in
match_parameter
integer
); |
Description
The source_string supports character datatypes (CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2, and NCLOB but not LONG). The pattern parameter is another name for the regular expression. match_parameter allows optional parameters such as handling the newline character, retaining multiline formatting, and providing control over case-sensitivity.
Parameters
source_string
Source string
pattern
The regular expression to match. The following special classes are supported:
| [:alpha:] Alphabetic characters |
| [:lower:] Lowercase alphabetic characters |
| [:upper:] Uppercase alphabetic characters |
| [:digit:] Numeric digits |
| [:alnum:] Alphanumeric characters |
| [:space:] Space characters (nonprinting), such as carriage return, newline, vertical tab, and form feed |
| [:punct:] Punctuation characters |
| [:cntrl:] Control characters (nonprinting) |
| [:print:] Printable characters |
match_parameter
This is null by default.
Examples
Example 24.312. Simple Use
The following SQL query's WHERE clause shows the REGEXP_LIKE operator, which 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.
SELECT postalcode FROM demo.demo.customers WHERE REGEXP_LIKE(postalcode, '[^[:digit:]]') PostalCode ========== WA1 1DP S-958 22 T2F 8M4 EC2 5NT 05432-043 . .
![[Note]](images/note.png)