REGEXP_SUBSTR oracle 10g feature

Learn how extract a sub-string from the given input string with example queries.

REGEXP_SUBSTR function is an extension of the SUBSTR function.

REGEXP_SUBSTR extractS a substring from a string using regular expression pattern matching.If the REGEXP_SUBSTR function does not find any occurrence of pattern, it will return NULL.

REGEXP_SUBSTR(<Source_string>, <Search_pattern>, <Start_position>, <nth_appearance>, <Match_modifier>)

Source_string – is the string to search.
Search_pattern – Is a regular expression to be matched.
Start_position(optional)-Is a positive integer that determines start position in the string that the function begins the search.The position defaults to 1, meaning that the function starts searching at the beginning of the string.

nth_appearance(optional)-It is the nth appearance of pattern in string. If omitted, it defaults to 1 which is the first appearance of pattern in string.

Match_modifier(optional)-It allows you to modify the matching behavior for the REGEXP_INSTR function. It can be a combination of the following
‘c’-Perform case-sensitive matching.
‘i’-Perform case-insensitive matching.

By default REGEXP_SUBSTR function Perform case-sensitive matching.

Example Queries

Extracting the first word from a string

SELECT REGEXP_SUBSTR('This is a oracle substring expression', 'sub')
  FROM DUAL;
  
 SELECT REGEXP_SUBSTR('This is a oracle substring expression', 'sub',4)
  FROM DUAL;

SELECT REGEXP_SUBSTR('This is a oracle substring expression', 'sub',1,1)
  FROM DUAL;
  
  SELECT REGEXP_SUBSTR('This is a oracle substring expression', 'sub',1,1,'i')
  FROM DUAL;

SELECT REGEXP_SUBSTR('This is a oracle substring expression', 'SUB',1,1,'i')
  FROM DUAL;

Output

sub

SELECT REGEXP_SUBSTR('This is a oracle substring expression', 'SUB',1,1,'i')
  FROM DUAL;
  
  SELECT REGEXP_SUBSTR('This is a oracle substring expression', 'SUB',1,1)
  FROM DUAL;

Output

NULL

Extract all non white Space characters

SELECT REGEXP_SUBSTR('This is a oracle substring expression', '[^ ]+')
  FROM DUAL;
  
SELECT REGEXP_SUBSTR('This is a oracle substring expression', '(\S*)(\s)')
  FROM DUAL;  
  
SELECT REGEXP_SUBSTR ('This is a oracle substring expression', '(\S*)')
FROM dual;

  SELECT REGEXP_SUBSTR('This is a oracle substring expression',  '(\w)*')
  FROM DUAL; 
  
  SELECT REGEXP_SUBSTR('This is a oracle substring expression',  '[[:alpha:][:digit:]]+')
  FROM DUAL; 

Output

This

Extract the 5th word in the string

SELECT REGEXP_SUBSTR('This is a oracle substring expression', '[^ ]+',1,5)
  FROM DUAL;
  
  SELECT REGEXP_SUBSTR('This is a oracle substring expression', '(\S*)(\s)',1,5)
  FROM DUAL;  
  
  SELECT REGEXP_SUBSTR('This is a oracle substring expression',  '(\W*)(\w)*',1,5)
  FROM DUAL; 
  
  SELECT REGEXP_SUBSTR('This is a oracle substring expression',  '[[:alpha:][:digit:]]+',1,5)
  FROM DUAL;  

Output
substring

Extract a digit from the given input string.

SELECT REGEXP_SUBSTR ('This is a oracle 11g and 12c substring expression', '(\d)')
FROM dual;

Output
1

Extract a first 2 digit number from the given input string

SELECT REGEXP_SUBSTR ('This is a oracle 11g and 12c substring expression', '(\d)(\d)')
FROM dual;

SELECT REGEXP_SUBSTR ('This is a oracle 11g and 12c substring expression', '(\d)(\d)',1,1)
FROM dual;

SELECT REGEXP_SUBSTR ('This is a oracle 11g and 12c substring expression', '(\d)+')
FROM dual;

SELECT REGEXP_SUBSTR ('This is a oracle 11g and 12c substring expression', '(\d)(\d)*')
FROM dual;

SELECT REGEXP_SUBSTR ('This is a oracle 11g and 12c substring expression', '(\d)+',1,1)
FROM dual;

Output
11

Extract a second 2 digit number from the given input string

SELECT REGEXP_SUBSTR ('This is a oracle 11g and 12c substring expression', '(\d)(\d)',1,2)
FROM dual;

SELECT REGEXP_SUBSTR ('This is a oracle 11g and 12c substring expression', '(\d)+',1,2)
FROM dual;

Output
12

Matching more than one alternative

The | pattern is used like an “OR” to specify more than one alternative.

Example query

SELECT REGEXP_SUBSTR ('This is a oracle 11g and 12c substring expression', 'oracle|11g|12c')
FROM dual;

Output

oracle