Oracle® Database SQL Reference 10g Release 1 (10.1) Part Number B10759-01 |
|
|
View PDF |
REGEXP_LIKE
is similar to the LIKE
condition, except REGEXP_LIKE
performs regular expression matching instead of the simple pattern matching performed by LIKE
. This condition evaluates strings using characters as defined by the input character set.
This condition complies with the POSIX regular expression standard and the Unicode Regular Expression Guidelines. For more information, please refer to Appendix C, " Oracle Regular Expression Support".
source_string
is a character expression that serves as the search value. It is commonly a character column and can be of any of the datatypes CHAR
, VARCHAR2
, NCHAR
, NVARCHAR2
, CLOB
, or NCLOB
.
pattern
is the regular expression. It is usually a text literal and can be of any of the datatypes CHAR
, VARCHAR2
, NCHAR
, or NVARCHAR2
. It can contain up to 512 bytes. If the datatype of pattern
is different from the datatype of source_string
, Oracle converts pattern
to the datatype of source_string
. For a listing of the operators you can specify in pattern
, please refer to Appendix C, " Oracle Regular Expression Support".
match_parameter
is a text literal that lets you change the default matching behavior of the function. You can specify one or more of the following values for match_parameter
:
'i'
specifies case-insensitive matching.
'c'
specifies case-sensitive matching.
'n'
allows the period (.), which is the match-any-character wildcard character, to match the newline character. If you omit this parameter, the period does not match the newline character.
'm'
treats the source string as multiple lines. Oracle interprets ^
and $
as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. If you omit this parameter, Oracle treats the source string as a single line.
If you specify multiple contradictory values, Oracle uses the last value. For example, if you specify 'ic'
, then Oracle uses case-sensitive matching. If you specify a character other than those shown above, then Oracle returns an error.
If you omit match_parameter
, then:
The default case sensitivity is determined by the value of the NLS_SORT
parameter.
A period (.) does not match the newline character.
The source string is treated as a single line.
The following query returns the first and last names for those employees with a first name of Steven or Stephen (where first_name
begins with Ste
and ends with en
and in between is either v
or ph)
:
SELECT first_name, last_name FROM employees WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$'); FIRST_NAME LAST_NAME -------------------- ------------------------- Steven King Steven Markle Stephen Stiles
The following query returns the last name for those employees with a double vowel in their last name (where last_name
contains two adjacent occurrences of either a
, e
, i
, o
, or u
, regardless of case):
SELECT last_name FROM employees WHERE REGEXP_LIKE (last_name, '([aeiou])\1', 'i'); LAST_NAME ------------------------- De Haan Greenberg Khoo Gee Greene Lee Bloom Feeney