REGEXP_SUBSTR Examples

REGEXP_SUBSTR Examples

The following example examines the string, looking for the first substring bounded by commas. Oracle Database searches for a comma followed by one or more occurrences of non-comma characters followed by a comma. Oracle returns the substring, including the leading and trailing commas.

SELECT
REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA',
',[^,]+,') "REGEXPR_SUBSTR"
FROM DUAL;

REGEXPR_SUBSTR
-----------------
, Redwood Shores,
The following example examines the string, looking for http:// followed by a substring of one or more alphanumeric characters and optionally, a period (.). Oracle searches for a minimum of three and a maximum of four occurrences of this substring between http:// and either a slash (/) or the end of the string.

SELECT
REGEXP_SUBSTR('http://www.oracle.com/products',
'http://([[:alnum:]]+\.?){3,4}/?') "REGEXP_SUBSTR"
FROM DUAL;

REGEXP_SUBSTR
----------------------
http://www.oracle.com/
The next two examples use the subexpr argument to return a specific subexpression of pattern. The first statement returns the first subexpression in pattern:

SELECT REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 1)
"REGEXP_SUBSTR" FROM DUAL;

REGEXP_SUBSTR
-------------------
123
The next statement returns the fourth subexpression in pattern:

SELECT REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 4)
"REGEXP_SUBSTR" FROM DUAL;

REGEXP_SUBSTR
-------------------
78