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