Purpose
This function allows you to search for a string using a regular expression and return the matching string, extending the functionality of the SUBSTR function. It is similar to REGEXP_INSTR, but instead of returning the position of the substring, it returns the substring itself. This function is particularly useful when you need only the content of the matched string and not its position within the source string.
Syntax
REGEXP_SUBSTR(source_char, pattern
[, position [, occurrence [, match_param [, subexpr] ] ] ]
)
Parameters
| Parameter | Description |
|---|---|
| source_char | The character expression to be used as the search value. The data type can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, or CLOB. |
| pattern | The regular expression extraction rule. It is typically a text literal, and the character type can be CHAR, VARCHAR2, NCHAR, or NVARCHAR2. |
| position | The starting position for the regular expression match, which is a positive integer. This is an optional parameter. The default value is 1, indicating that the search for source_char starts from the first character. |
| occurrence | The number of the regular expression match in source_char. This is an optional parameter. The default value is 1. If occurrence is greater than 1, the search for the second regular expression match starts from the first character after the first match, and so on. |
| match_param | The parameter to modify the default matching behavior of the regular expression. This is an optional parameter. It is a character expression of the VARCHAR2 or CHAR data type.
|
| subexpr | The subexpression in pattern to be returned. It is a non-negative integer from 0 to 9. This is an optional parameter. The default value is 0, indicating that the first subexpression is returned. |
Return type
Returns a VARCHAR2 or CLOB data type that is the same as the source_char parameter.
Examples
Split
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 setReturn the third subexpression of 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