Purpose
REGEXP_INSTR() returns the location of the substring that matches a regular expression pattern in the source string.
Syntax
REGEXP_INSTR (source_char, pattern
[, position[, occurrence[, return_opt[, 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. |
| return_opt | Specifies what should be returned in relation to the occurrence. This parameter is optional. Default value: 0.
|
| 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 position of the first subexpression that matches the pattern is returned. |
Return type
The return type is NUMBER.
Examples
The following example returns the positions of the first and last characters of the fourth subexpression anB of the expression (Oc)(e(anB)(ase)) in the string OceanBase.
obclient> SELECT REGEXP_INSTR('OceanBase', '(Oc)(e(anB)(ase))',1, 1, 0, 'i', 3) "first",
REGEXP_INSTR('OceanBase', '(Oc)(e(anB)(ase))', 1, 1, 1, 'i', 3)-1 "last"
FROM DUAL;
+-------+------+
| first | last |
+-------+------+
| 4 | 6 |
+-------+------+
1 row in set
