Syntax
REGEXP_INSTR(expr, pat [, pos [, occurrence [, return_option [, match_type ]]]])
Purpose
You can call this function to return the start position of the substring in the expr expression that matches the pat expression. If no such substrings exist, the function returns 0. If expr or pat is NULL, the function returns NULL. The return value of the position starts from 1.
posindicates the start position for character search in theexprexpression. The default value is1.occurrencespecifies the ordinal number of occurrence ofpatinexpr. The default value is1.return_optionspecifies the type of the return value. The default value is0.The value
0indicates thatREGEXP_INSTR()returns the position of the first character of the substring that matches pat.The value
1indicates thatREGEXP_INSTR()returns the position of the last character of the substring that matches pat.
match_typeindicates the matching rule. For information about 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 (0.01 sec)
obclient> SELECT REGEXP_INSTR('ocean base oceanbase', 'ocean',2);
+-------------------------------------------------+
REGEXP_INSTR('ocean base oceanbase', 'ocean',2)
+-------------------------------------------------+
12
+-------------------------------------------------+
1 row in set (0.00 sec)
obclient> SELECT REGEXP_INSTR('ocean base oceanbase', 'ocean',1,2);
+---------------------------------------------------+
REGEXP_INSTR('ocean base oceanbase', 'ocean',1,2)
+---------------------------------------------------+
12
+---------------------------------------------------+
1 row in set (0.00 sec)
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 (0.01 sec)
