INTRODUCTION

The Oracle REGEXP_SUBSTR function is one of the most powerful string-processing functions available in Oracle SQL.

It allows you to extract text using regular expression patterns instead of fixed positions.

This makes it extremely useful for:

• parsing email addresses,
• extracting numbers,
• splitting strings,
• processing log files,
• validating text,
• and handling semi-structured data.

This is another article of the series of unjustly ignored features, we aim to explore its practical real-life examples that are easy to understand and reproduce.

What is REGEXP_SUBSTR?

The REGEXP_SUBSTR function searches a string using a regular expression pattern and returns the matching substring.

Basic Syntax

REGEXP_SUBSTR(
    source_string,
    pattern,
    position,
    occurrence,
    match_parameter,
    subexpression
)

Parameters Explained

Examples

Create Sample Table

Let us create a simple table containing realistic text values.

CREATE TABLE customer_data (
    id NUMBER,
    contact_info VARCHAR2(200)
);

Insert sample data.

INSERT INTO customer_data VALUES
(1, 'John Doe | john.doe@email.com | +1-555-1001');
INSERT INTO customer_data VALUES
(2, 'Emma Smith | emma.smith@email.com | +1-555-2002');
INSERT INTO customer_data VALUES
(3, 'Liam Brown | liam.b@email.com | +1-555-3003');
COMMIT;

Now, the data in the table is:

        ID CONTACT_INFO                                                                                                                                                                                            
---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         1 John Doe | john.doe@email.com | +1-555-1001                                                                                                                                                             
         2 Emma Smith | emma.smith@email.com | +1-555-2002                                                                                                                                                         
         3 Liam Brown | liam.b@email.com | +1-555-3003        

Example 1 — Extract Email Address

One of the most common real-world use cases is extracting email addresses.

To achieve that, use REGEXP_SUBSTR:

SELECT contact_info,
       REGEXP_SUBSTR(
           contact_info,
           '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}'
       ) AS email
FROM customer_data;

output:

CONTACT_INFO                             EMAIL               
---------------------------------------- --------------------
John Doe | john.doe@email.com | +1-555-1 john.doe@email.com  
001                                                          
Emma Smith | emma.smith@email.com | +1-5 emma.smith@email.com
55-2002                                                      
Liam Brown | liam.b@email.com | +1-555-3 liam.b@email.com    
003                                                          

Result

If ti change the query for the name by using the Substr function:

SELECT Substr(contact_info,1,10) as CONTACT_INFO,
       REGEXP_SUBSTR(
           contact_info,
           '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}'
       ) AS email
FROM customer_data;

please note the usage of the Oracle legacy SUBSTR function also!

Understanding the Pattern

[A-Za-z0-9._%+-]+

Matches:

• letters,
• numbers,
• dots,
• underscores,
• ==> email username characters

@

Matches the @ symbol.

[A-Za-z0-9.-]+

Matches the domain name.

\.[A-Za-z]{2,}

Matches domain extensions like:

• .com
• .org
• .net

Example 2 — Extract Phone Number

E.g.

SELECT contact_info,
       REGEXP_SUBSTR(
           contact_info,
           '\+[0-9-]+'
       ) AS phone_number
FROM customer_data;

or, if we want to have just the name displayed, we use SUBSTR:

SELECT Substr(contact_info,1,10) as contact_info,
       REGEXP_SUBSTR(
           contact_info,
           '\+[0-9-]+'
       ) AS phone_number
FROM customer_data;

for an output:

ONTACT_INFO                             PHONE_NUMBER                                                                                                                                                                                            
---------------------------------------- -----------
John Doe |                               +1-555-1001                                                                                                                                                                                             
Emma Smith                               +1-555-2002                                                                                                                                                                                             
Liam Brown                               +1-555-3003 

a simpler version of the phone numbers list only:

SELECT REGEXP_SUBSTR(
           contact_info,
           '\+[0-9-]+'
       ) AS phone_number
FROM customer_data;

Result

Example 3 — Extract First Word

This is useful for extracting first names from full text.

Sample sql:

SELECT SUBSTR(contact_info,1,8) as "Contact ID",
       REGEXP_SUBSTR(
           contact_info,
           '^[^ ]+'
       ) AS first_word
FROM customer_data;

Output:

Contact  FIRST_WO
-------- --------
John Doe John    
Emma Smi Emma    
Liam Bro Liam  

Explanation

^

Means start of string.

[^ ]+

Matches characters until the first space.

Example 4 — Extract Numbers from Text

Suppose you have invoice strings.

SELECT REGEXP_SUBSTR(
           'Invoice-2026-4501',
           '[0-9]+'
       ) AS first_number
FROM dual;
-- output:
FIRS
----
2026

Result

If I want to get the second number occurrence:

SELECT REGEXP_SUBSTR(
           'Invoice-2026-4501',
           '[0-9]+'
           ,
           1,
           2
       ) AS first_number
FROM dual;
-- output:
FIRS
----
4501

Note that if I would look for the third occurrence, the same QL will return NULL as there are no more numerical occurrences

Example 5 — Return Second Occurrence

You can extract a specific occurrence of a match.

SELECT REGEXP_SUBSTR(
           'A-100 B-200 C-300',
           '[0-9]+',
           1,
           2
       ) AS second_number
FROM dual;

Third occurrence of a number:

Example 6 — Split Comma-Separated Values

A very common Oracle SQL requirement.

SELECT REGEXP_SUBSTR(
           'RED,GREEN,BLUE',
           '[^,]+',
           1,
           1
       ) AS first_value
FROM dual;

Output:

FIR
---
RED

Result

Retrieve second value:

SELECT REGEXP_SUBSTR(
           'RED,GREEN,BLUE',
           '[^,]+',
           1,
           2
       ) AS second_value
FROM dual;

-Output:

SECON
-----
GREEN

Result

Split values separated by other characters

E.g., If I want to extract values separated by semicolon, this will give me the second pattern match:

SELECT REGEXP_SUBSTR(
           'RED,GREEN;BLUE',
           '[^;]+',
           1,
           2
       ) AS first_value
FROM dual;

output:

SEMI
----
BLUE

Example 7 — Extract Domain Name from Email

Example:

SELECT REGEXP_SUBSTR(
           'john.doe@email.com',
           '@(.+)$',
           1,
           1,
           NULL,
           1
       ) AS domain_name
FROM dual;

Result

Understanding Subexpression Parameter

The final parameter:

1

returns the captured group:

(.+)

instead of the entire match.

This is extremely useful in advanced text parsing.

So I f I run with the parameter, I get:

but if I remove the parameter value, I do not get anyhting back:

Example 8 — Extract File Extension

Example:

SELECT REGEXP_SUBSTR(
           'backup_database_01.zip',
           '\.[A-Za-z0-9]+$'
       ) AS extension
FROM dual;

Result

Example 9 — Extract Date from Text

SELECT REGEXP_SUBSTR(
           'Order Date: 2026-05-15',
           '[0-9]{4}-[0-9]{2}-[0-9]{2}'
       ) AS extracted_date
FROM dual;

Result

Common Match Parameters

Example:

SELECT REGEXP_SUBSTR(
           'Oracle DATABASE',
           'database',
           1,
           1,
           'i'
       ) AS match_result
FROM dual;

while:

Comparison table REGEXP_SUBSTR vs SUBSTR

Performance Considerations

Regular expressions are powerful but can be slower than simple string functions.

Recommendations:

• use SUBSTR when possible,
• avoid overly complex regex patterns,
• test performance on large datasets,
• use indexes where appropriate.

Common Real-Life Use Cases

REGEXP_SUBSTR is commonly used for:

• extracting emails,
• parsing log files,
• splitting CSV data,
• validating user input,
• extracting IDs,
• processing filenames,
• and handling semi-structured data.

Conclusion

The Oracle REGEXP_SUBSTR function is an essential tool for advanced text processing in SQL.

Key advantages:

• flexible pattern matching,
• simplified text extraction,
• reduced procedural code,
• and powerful parsing capabilities.

As soon as one becomes familiar with regular expressions, REGEXP_SUBSTR can significantly simplify many real-world Oracle SQL tasks and one can start using the REGEXP* functions along side the legacy functions delivered by Oracle.

My personal conclusion would also be that text manipulation in the oracle database is a lot of fun and I promise to follow up with more posts in this area!