Oracle REGEXP_LIKE Function

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.

Basic 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)

Try it

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)

Try it

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)

Try it

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)

Try it

Matching 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)

Try it

Summary #

• Use the Oracle REGEXP_LIKE() function to match data based on a regular expression pattern.