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.337. 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)