Syntax
REGEXP_INSTR(expr, pat [, pos [, occurrence [, return_option [, match_type ]]]])
Purpose
Returns the starting position of the substring in expr that matches the pattern pat. If no matching substring is found, it returns 0. If expr or pat is NULL, the function also returns NULL. The returned position starts from 1.
posspecifies the starting position inexprfor the search, defaulting to1.occurrencespecifies the occurrence ofpatinexpr, defaulting to1.return_optionspecifies the type of the return value, defaulting to0.When
return_optionis0,REGEXP_INSTR()returns the position of the first matched character.When
return_optionis1,REGEXP_INSTR()returns the position of the last matched character.
match_typespecifies the matching rule. For more information about the matching rules, 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