REGEXP_LIKE – oracle regular expression

Learn how to retrieve the matching strings from the table using pattern matching with regular expressions.

The Oracle REGEXP_LIKE condition allows you to perform regular expression matching in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement.

REGEXP_LIKE is similar to the LIKE condition, except REGEXP_LIKE performs regular expression matching instead of the simple pattern matching performed by LIKE.
This condition evaluates strings using characters as defined by the input character set.

Below is the syntax

REGEXP_LIKE(source_string, search_pattern [, match_parameter]);

source_string -the string expression.

search_pattern -the regular expression matching pattern

match_parameter -is a text literal that lets you change the default matching behavior of the function.

You can specify one or more of the following values for match_parameter:

1. ‘i’ specifies case-insensitive matching.
2. ‘c’ specifies case-sensitive matching.
3. ‘n’ allows the period (.), which is the match-any-character wildcard character, to match the newline character. If you omit this parameter, the period does not match the newline character.
4. ‘m’ treats the source string as multiple lines. Oracle interprets ^ and $ as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. If you omit this parameter, Oracle treats the source string as a single line.

Notes:
The REGEXP_LIKE() function returns rows that match the regular expression pattern.

For base table reference please click on the following link

Example Queries

1. Find the names that starts with A.

SELECT ENAME 
FROM EMP
WHERE REGEXP_LIKE(ENAME, '^A(*)');

or

SELECT ENAME 
FROM EMP
WHERE REGEXP_LIKE(ENAME, '^A');

2. Find the names that ends with N.

SELECT ENAME 
FROM EMP
WHERE REGEXP_LIKE(ENAME, '(*)N$');

SELECT ENAME 
FROM EMP
WHERE REGEXP_LIKE(ENAME, 'N$');

3. Find the names that contains A.

SELECT ENAME 
FROM EMP
WHERE REGEXP_LIKE(ENAME, 'A');

4. Find the names that contains ER .

SELECT ENAME 
FROM EMP
WHERE REGEXP_LIKE(ENAME, 'ER');

5.Find the names that starts with A or S

SELECT ENAME 
FROM EMP
WHERE REGEXP_LIKE(ENAME, '^A|^S');

SELECT ENAME 
FROM EMP
WHERE REGEXP_LIKE(ENAME, '^[AS]');

SELECT ENAME 
FROM EMP
WHERE REGEXP_LIKE(ENAME, '^(A|S)');

6.Find the names that starts with M and second letter should be either A or I.

SELECT ENAME 
FROM EMP
WHERE REGEXP_LIKE(ENAME, '^M(A|I)');

7.Find the names that contains exactly two letters T.

SELECT ENAME 
FROM EMP
WHERE REGEXP_LIKE(ENAME, 'T{2}');

SELECT ENAME 
FROM EMP
WHERE REGEXP_LIKE(ENAME, 'T{2}','c');

SELECT ENAME 
FROM EMP
WHERE REGEXP_LIKE(ENAME, 't{2}','i');

8. Find the names with the double adjacent Letter.

SELECT ENAME 
FROM EMP
WHERE REGEXP_LIKE(ENAME, '([TL])\1','i');

SELECT ENAME 
FROM EMP
WHERE REGEXP_LIKE(ENAME, '([A-Z])\1','i');

9. Find all names that contain a letter in the range of ‘d’ and ‘g’, followed by the letter ‘a’.

SELECT ENAME 
FROM EMP
WHERE REGEXP_LIKE(ENAME, '[d-g]a','i');

10. Find all names that contain a letter in the range of ‘a’ and ‘z’,followed by any character followed by the letter ‘a’.

SELECT ENAME 
FROM EMP
WHERE REGEXP_LIKE(ENAME, '[a-z].a','i');