Purpose
This function allows you to search for a string using a regular expression and return the matched 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 only need the content of the matched string and do not require its position in 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. It can be of the CHAR, VARCHAR2, NCHAR, NVARCHAR2, or CLOB data type. |
| pattern | The regular expression extraction rule. It is typically a text literal and can be of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type. |
| position | The starting position for the regular expression match. It is a positive integer and is optional. 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. It is optional. 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 character expression that specifies the modified regular expression matching mode. It is optional. It can be 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 and is optional. 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 setExtract the first
<p>tag and its content from the string<div><p>Hello</p><p>World</p></div>.obclient [SYS]> SELECT REGEXP_SUBSTR('<div><p>Hello</p><p>World</p></div>', '<p>.*?</p>') FROM DUAL; +-------------------------------------------------------------------+ | REGEXP_SUBSTR('<DIV><P>HELLO</P><P>WORLD</P></DIV>','<P>.*?</P>') | +-------------------------------------------------------------------+ | <p>Hello</p> | +-------------------------------------------------------------------+ 1 row in set