Purpose
REGEXP_SUBSTR() is an extension of the SUBSTR function. It searches a string for a regular expression pattern and returns the matching substring. This function is similar to REGEXP_INSTR. However, instead of returning the position of the substring, it returns the substring itself. This function is useful if you need only the matching portion of the source string but not its position in the source string.
Syntax
REGEXP_SUBSTR(source_char, pattern
[, position [, occurrence [, match_param [, subexpr] ] ] ]
)
Parameters
| Parameter | Description |
|---|---|
| source_char | A character expression that serves as the search value. It can be of the CHAR, VARCHAR2, NCHAR, NVARCHAR2, or CLOB data type. |
| pattern | The regular expression to find a substring to extract. It is usually a text literal and can be of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type. |
| position | The position where the function begins searching for a match. This optional parameter is a positive integer. The default value is 1, which means that the function begins searching at the first character of source_char. |
| occurrence | The occurrence of a pattern match in source_char. This parameter is optional. The default value is 1. If occurrence is greater than 1, the function searches for the second occurrence starting from the first character following the first occurrence of the regular expression pattern, and so forth. |
| match_param | The default matching behavior of the function. This parameter is optional. It is a character expression of the VARCHAR2 or CHAR data type.
|
| subexpr | A non-negative integer from 0 to 9 that indicates which subexpression in pattern is to be returned by the function. This parameter is optional. The default value is 0, which means that the first subexpression is to be returned. |
Return type
The return type is VARCHAR2 or CLOB, same as the data type of source_char.
Examples
The following example splits
abc,def,ghiintoabc,def, andghi.obclient> SELECT REGEXP_SUBSTR('abc,def,ghi', '[^,]+',1,1) AS "First", REGEXP_SUBSTR('abc,def,ghi', '[^,]+',1,2) AS "Second", REGEXP_SUBSTR('abc,def,ghi', '[^,]+',1,3) AS "Third" FROM DUAL; +-----------+-----------+-----------+ | First | Second | Third | +-----------+-----------+-----------+ | abc | def | ghi | +-----------+-----------+-----------+ 1 row in setThe following example returns the third subexpression in the expression
abc(d(e(f)gh)i).obclient> SELECT REGEXP_SUBSTR('abcdefghi', 'abc(d(e(f)gh)i)',1,1,'i',2) FROM DUAL; +--------------------------------------------------------+ | REGEXP_SUBSTR('ABCDEFGHI','ABC(D(E(F)GH)I)',1,1,'I',2) | +--------------------------------------------------------+ | efgh | +--------------------------------------------------------+ 1 row in set