Site icon Radu Pârvu

Understanding REGEXP_SUBSTR: Syntax and Practical Examples

Radu PARVU

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:

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

ParameterDescription
source_stringText to search
patternRegular expression pattern
positionStarting position
occurrenceWhich occurrence to return
match_parameterMatching options
subexpressionCapturing group to return

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;
CONTACT_INFOEMAIL
John Doe …john.doe@email.com
Emma Smith …emma.smith@email.com
Liam Brown …liam.b@email.com

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

Understanding the Pattern

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

Matches:

@

Matches the @ symbol.

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

Matches the domain name.

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

Matches domain extensions like:

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

PHONE_NUMBER
+1-555-1001
+1-555-2002
+1-555-3003

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

FIRST_NUMBER
2026

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

FIRST_VALUE
RED

Retrieve second value:

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

-Output:

SECON
-----
GREEN

Result

SECOND_VALUE
GREEN

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

DOMAIN_NAME
email.com

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

EXTENSION
.zip

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

EXTRACTED_DATE
2026-05-15

Common Match Parameters

ParameterMeaning
iCase-insensitive matching
cCase-sensitive matching
nDot matches newline
mMulti-line mode

Example:

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

while:

Comparison table REGEXP_SUBSTR vs SUBSTR

SUBSTRREGEXP_SUBSTR
Position-basedPattern-based
Simple extractionAdvanced extraction
Faster for simple casesFlexible for complex text
No regex supportFull regex support

Performance Considerations

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

Recommendations:

Common Real-Life Use Cases

REGEXP_SUBSTR is commonly used for:

Conclusion

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

Key advantages:

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!

Exit mobile version