Oracle REGEXP_LIKE

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