Use of REGEXP_LIKE in PL/SQL

4 min readApr 6, 2021

What is PL/SQL?

Oracle PL/SQL is an extension of SQL language that combines the data manipulation power of SQL with the processing power of procedural language to create super powerful SQL queries. PL/SQL ensures seamless processing of SQL statements by enhancing the security, portability, and robustness of the Database.

What is REGEXP ??

Regular expressions enable you to search for patterns in string data by using standardized syntax conventions. You specify a regular expression by means of the following types of characters:

Meta-characters, which are operators that specify search algorithms
Literals, which are the characters for which you are searching

A regular expression can specify complex patterns of character sequences. For example, the following regular expression searches for the literals f or ht, the t literal, the p literal optionally followed by the s literal, and finally the colon (:) literal. Regular expressions are a powerful text processing component of programming languages such as Perl and Java.

Different types of REGEXP in PL/SQL:

Following are the four functions you’ll use to work with regular expressions in Oracle:

REGEXP_LIKE

Determines whether a specific column, variable, or text literal contains text matching a regular expression.

2. REGEXP_INSTR

Locates, by character position, an occurrence of text matching a regular expression.

3. REGEXP_REPLACE

Replaces text matching a regular expression with new text that you specify. Your replacement text can include back references to values in the regular expression.

4. REGEXP_SUBSTR

Extracts text matching a regular expression from a character column, variable, or text literal.

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

Syntax

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.

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;

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

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;

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;

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;

Practicing REGEXP on Hacker-Rank

EXAMPLE 1

Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION. Your result cannot contain duplicates.
Input Format

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.

code snippet:-

SELECT DISTINCT CITY
FROM STATION
WHERE REGEXP_LIKE(City, '[aeiou]$');

EXAMPLE 2

Query the list of CITY names from STATION which have vowels (i.e., a, e, i, o, and u) as both their first and last characters. Your result cannot contain duplicates.
Input Format

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.