Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle String Functions / Oracle REGEXP_LIKE

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]);

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.

employees table

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;
Oracle REGEXP_LIKE - first names contain letter c

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' );
Oracle REGEXP_LIKE - first name starts with letter a

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;
Oracle REGEXP_LIKE - first names end with letter y

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;
Oracle REGEXP_LIKE - first names start with letter m or n

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;
Oracle REGEXP_LIKE - first names have two letter l

In this tutorial, you have learned how to use the Oracle REGEXP_LIKE() function to match data based on a regular expression pattern.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle REGEXP_REPLACE
Next Oracle RPAD

Oracle Functions

  • Aggregate Functions
  • Analytic Functions
  • Comparison Functions
  • Date Functions
  • String Functions

String Functions

  • ASCII
  • CHR
  • CONCAT
  • CONVERT
  • DUMP
  • INSTR
  • INITCAP
  • LENGTH
  • LOWER
  • LPAD
  • LTRIM
  • REPLACE
  • REGEXP_COUNT
  • REGEXP_INSTR
  • REGEXP_LIKE
  • REGEXP_REPLACE
  • REGEXP_SUBSTR
  • RPAD
  • RTRIM
  • SOUNDEX
  • SUBSTR
  • TRANSLATE
  • TRIM
  • UPPER

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.