The Oracle REGEXP_LIKE() function is an advanced version of the LIKE operator. The REGEXP_LIKE() function returns rows that match a regular expression pattern.
Noted that in SQL standard, REGEXP_LIKE is an operator instead of a function.
Syntax
The following illustrates the syntax of the Oracle REGEXP_LIKE() function:
REGEXP_LIKE(source_string, search_pattern [, match_parameter]);
Code language: SQL (Structured Query Language) (sql)Arguments
The REGEXP_LIKE() function accepts 3 arguments:
1) source_string
is a string for which to be searched. Typically, it is a character column of any data type CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
2) search_pattern
is a literal string that represents the regular expression pattern to be matched.
3) match_parameter
is a literal string that changes the default matching behavior of the REGEXP_LIKE() function.
The behavior of this match_parameter in this function is the same as the one in the REGEXP_SUBSTR() function. Please refer to REGEXP_SUBSTR() function for detailed information.
Return Value
The REGEXP_LIKE() function returns rows that match the regular expression pattern.
Examples
We will use the employees table in the sample database for the demonstration.
A) Simple REGEXP_LIKE() example.
The following statement returns first names that contain the letter ‘c’:
SELECT
first_name
FROM
employees
WHERE
REGEXP_LIKE( first_name, 'c' )
ORDER BY first_name;Code language: SQL (Structured Query Language) (sql)
B) Matching the beginning of the line
The caret (^) operator matches the beginning of the line. The following query returns employees whose first names start with the letter A:
SELECT
last_name
FROM
employees
WHERE
REGEXP_LIKE( last_name, '^a', 'i' );Code language: SQL (Structured Query Language) (sql)
C) Matching the end of the line
The dollar ($) operator matches the end of the line. The following example returns the first names that end with a letter y:
SELECT
first_name
FROM
employees
WHERE
REGEXP_LIKE( first_name, 'y$', 'i' )
ORDER BY
first_name; Code language: SQL (Structured Query Language) (sql)
D) Matching either a or b
The pipe (|) operator e.g., a |b matches either a or b. The following statement returns employees whose first names start with either letter m or n :
SELECT
first_name
FROM
employees
WHERE
REGEXP_LIKE(first_name,'^m|^n','i')
ORDER BY first_name; Code language: SQL (Structured Query Language) (sql)
E) Match a preceding character exactly n times
To match a preceding character exactly n times, you use the char{n} pattern. The following example returns the first names that contain exactly two letters L or 'l':
SELECT
first_name
FROM
employees
WHERE
REGEXP_LIKE( first_name, 'l{2}', 'i' )
ORDER BY
first_name;Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned how to use the Oracle REGEXP_LIKE() function to match data based on a regular expression pattern.
