Топ-100
 

REGEXP_INSTR

RDBMS Type: MySQL
Topic

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

Returns the starting index of the substring of the string expr that
matches the regular expression specified by the pattern pat, 0 if there
is no match. If expr or pat is NULL, the return value is NULL.
Character indexes begin at 1.

REGEXP_INSTR() takes these optional arguments:

o pos: The position in expr at which to start the search. If omitted,
the default is 1.

o occurrence: Which occurrence of a match to search for. If omitted,
the default is 1.

o return_option: Which type of position to return. If this value is 0,
REGEXP_INSTR() returns the position of the matched substring's first
character. If this value is 1, REGEXP_INSTR() returns the position
following the matched substring. If omitted, the default is 0.

o match_type: A string that specifies how to perform matching. The
meaning is as described for REGEXP_LIKE().

For additional information about how matching occurs, see the
description for REGEXP_LIKE().

URL: https://dev.mysql.com/doc/refman/8.0/en/regexp.html

Example

mysql> SELECT REGEXP_INSTR('dog cat dog', 'dog');
+------------------------------------+
| REGEXP_INSTR('dog cat dog', 'dog') |
+------------------------------------+
| 1 |
+------------------------------------+
mysql> SELECT REGEXP_INSTR('dog cat dog', 'dog', 2);
+---------------------------------------+
| REGEXP_INSTR('dog cat dog', 'dog', 2) |
+---------------------------------------+
| 9 |
+---------------------------------------+
mysql> SELECT REGEXP_INSTR('aa aaa aaaa', 'a{2}');
+-------------------------------------+
| REGEXP_INSTR('aa aaa aaaa', 'a{2}') |
+-------------------------------------+
| 1 |
+-------------------------------------+
mysql> SELECT REGEXP_INSTR('aa aaa aaaa', 'a{4}');
+-------------------------------------+
| REGEXP_INSTR('aa aaa aaaa', 'a{4}') |
+-------------------------------------+
| 8 |
+-------------------------------------+