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. This function 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 the content of the matching string but not its position within the source string.
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, with a character type of 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, which means the search starts from the first character of source_char. |
| 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 regular expression matching mode. It is optional. The data type is VARCHAR2 or CHAR.
|
| 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, which means the first subexpression is returned. |
Return type
The return type is the same as the VARCHAR2 or CLOB data type of 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