Declaration
REGEXP_INSTR(expr, pat [, pos [, occurrence [, return_option [, match_type ]]]])
Description
Returns the starting position of the first substring that matches the pattern pat in the expression expr. If no matching substring is found, returns 0. If either expr or pat is NULL, the function returns NULL. The returned position is 1-based.
pos: The position in the string to start the search. The default value is1.occurrence: the position inexprwherepatoccurs for the first time. The default value is1.return_optionindicates the type of the return value. The default value is0.When the value is
0, it indicates that REGEXP_INSTR() returns the position of the first matched character.When the value is
1, it indicates that REGEXP_INSTR() returns the position of the last matched character.
match_typespecifies the matching rule. For more information, see REGEXP_LIKE.
Examples
obclient> SELECT REGEXP_INSTR('ocean base oceanbase', 'ocean');
+-----------------------------------------------+
| REGEXP_INSTR('ocean base oceanbase', 'ocean') |
+-----------------------------------------------+
| 1 |
+-----------------------------------------------+
1 row in set
obclient> SELECT REGEXP_INSTR('ocean base oceanbase', 'ocean',2);
+-------------------------------------------------+
| REGEXP_INSTR('ocean base oceanbase', 'ocean',2) |
+-------------------------------------------------+
| 12 |
+-------------------------------------------------+
1 row in set
obclient> SELECT REGEXP_INSTR('ocean base oceanbase', 'ocean',1,2);
+---------------------------------------------------+
| REGEXP_INSTR('ocean base oceanbase', 'ocean',1,2) |
+---------------------------------------------------+
| 12 |
+---------------------------------------------------+
1 row in set
obclient> SELECT REGEXP_INSTR('ocean base oceanbase', 'ocean',1,2,1);
+-----------------------------------------------------+
| REGEXP_INSTR('ocean base oceanbase', 'ocean',1,2,1) |
+-----------------------------------------------------+
| 17 |
+-----------------------------------------------------+
1 row in set