REGEXP_INSTR

Syntax

REGEXP_INSTR(expr, pat [, pos [, occurrence [, return_option [, match_type ]]]])

Description

REGEXP_INSTR returns the starting position of the substring in expr that matches the pattern pat. If no substring matches the pattern, it returns 0. If expr or pat is NULL, it returns NULL. The position starts from 1.

• pos specifies the starting position in expr for the search. The default value is 1.

• occurrence specifies the occurrence of the pattern in expr. The default value is 1.

• return_option specifies the type of the return value. The default value is 0.

  • When return_option is 0, REGEXP_INSTR() returns the position of the first character that matches the pattern.

  • When return_option is 1, REGEXP_INSTR() returns the position of the last character that matches the pattern.

• match_type specifies the matching rule. For more information about the matching rules, see REGEXP_LIKE.

Examples

SELECT REGEXP_INSTR('ocean base oceanbase', 'ocean');
+-----------------------------------------------+
| REGEXP_INSTR('ocean base oceanbase', 'ocean') |
+-----------------------------------------------+
|                                             1 |
+-----------------------------------------------+
1 row in set (0.001 sec)

SELECT REGEXP_INSTR('ocean base oceanbase', 'ocean',2);
+-------------------------------------------------+
| REGEXP_INSTR('ocean base oceanbase', 'ocean',2) |
+-------------------------------------------------+
|                                               12 |
+-------------------------------------------------+
1 row in set (0.001 sec)

SELECT REGEXP_INSTR('ocean base oceanbase', 'ocean',1,2);
+---------------------------------------------------+
| REGEXP_INSTR('ocean base oceanbase', 'ocean',1,2) |
+---------------------------------------------------+
|                                                12 |
+---------------------------------------------------+
1 row in set (0.001 sec)

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.001 sec)