Purpose
This function returns the position of the match in the source string.
Syntax
REGEXP_INSTR (source_char, pattern
[, position[, occurrence[, return_opt[, 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 usually a text literal. 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, which indicates that 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. |
| return_opt | The option for returning the position of the matched value. It is optional. The default value is 0.
|
| match_param | The option for modifying the default regular expression matching method. It is optional. It is a character expression of the VARCHAR2 or CHAR data type.
|
| subexpr | The subexpression to be returned from pattern. It is a nonnegative integer from 0 to 9 and is optional. The default value is 0, which indicates that the position of the first subexpression is returned. |
Return type
NUMBER
Examples
Return the positions of the first and last characters of the fourth subexpression anB in the string OceanBase for the expression (Oc)(e(anB)(ase)).
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