Prev Up Next
regexp_instr Home registry_get

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

Example24.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] Compatibility:

The regular expressions supported here follow version 7.9 of the Perl Compatible Regular Expression (PCRE) syntax.

  1. Wikipedia

  2. PCRE.org

Prev Up Next
regexp_instr Home registry_get