Syntax
------

REGEXP_INSTR(subject, pattern)

Returns the position of the first occurrence of the regular expression pattern
in the string subject, or 0 if pattern was not found.

The positions start with 1 and are measured in characters (i.e. not in bytes),
which is important for multi-byte character sets. You can cast a multi-byte
character set to BINARY to get offsets in bytes.

The function follows the case sensitivity rules of the effective collation.
Matching is performed case insensitively for case insensitive collations, and
case sensitively for case sensitive collations and for binary data.

The collation case sensitivity can be overwritten using the (?i) and (?-i)
PCRE flags.

MariaDB uses the PCRE regular expression library for enhanced regular
expression performance, and REGEXP_INSTR was introduced as part of this
enhancement.

Examples
--------

SELECT REGEXP_INSTR('abc','b');
-> 2

SELECT REGEXP_INSTR('abc','x');
-> 0

SELECT REGEXP_INSTR('BJÖRN','N');
-> 5

Casting a multi-byte character set as BINARY to get offsets in bytes:

SELECT REGEXP_INSTR(BINARY 'BJÖRN','N') AS cast_utf8_to_binary;
-> 6

Case sensitivity:

SELECT REGEXP_INSTR('ABC','b');
-> 2

SELECT REGEXP_INSTR('ABC' COLLATE utf8_bin,'b');
-> 0

SELECT REGEXP_INSTR(BINARY'ABC','b');
-> 0

SELECT REGEXP_INSTR('ABC','(?-i)b');
-> 0

SELECT REGEXP_INSTR('ABC' COLLATE utf8_bin,'(?i)b');
-> 2

URL: https://mariadb.com/kb/en/regexp_instr/