Purpose
This function allows you to search for strings using regular expressions and return the matched strings, extending the functionality of the SUBSTR function. This function is similar to REGEXP_INSTR, but instead of returning the position of the substring, it returns the substring itself. If you only need the content of the matched string and do not require its position in the source string, this function is ideal.
Syntax
REGEXP_SUBSTR(source_char, pattern
[, position [, occurrence [, match_param [, subexpr] ] ] ]
)
Parameters
Parameter |
Description |
|---|---|
| source_char | A 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. 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 | A character expression specifying the modified regular expression matching mode. It is optional. The data type is VARCHAR2 or CHAR.
|
| subexpr | The subexpression of 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
The return type is the same as the source_char parameter, which can be VARCHAR2 or CLOB.
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