MariaDB REGEXP_INSTR() Function

The MariaDB REGEXP_INSTR() function returns the starting index of a regular expression pattern in the given string. It returns 0 if there is no match. If the string or pattern is NULL, the return value will be NULL. Character indexes begin at 1.

The REGEXP_INSTR() function performs a case-insensitive match, except when used with binary strings.

Syntax

REGEXP_INSTR(string, pattern)

Parameters

Regular Expression Patterns

List of standard class name

Example: REGEXP_INSTR() examples

The example below shows the usage of REGEXP_INSTR() function.

SELECT REGEXP_INSTR('dog cat dog', 'dog');
Result: 1

SELECT REGEXP_INSTR('aa aaa aaaa', 'a{2}');
Result: 1

SELECT REGEXP_INSTR('aa aaa aaaa', 'a{4}');
Result: 8

Example: Case-sensitive search

There are many ways in MariaDB which can be used for case-sensitive search while using this function. See the example below:

SELECT REGEXP_INSTR('ABC','b');
Result: 2

/* using COLLATE to convert string to a binary string */
SELECT REGEXP_INSTR('ABC' COLLATE utf8_bin,'b');
Result: 0

/* using BINARY to convert string to a binary string */
SELECT REGEXP_INSTR(BINARY'ABC','b');
Result: 0

/* forces case-sensitive using (?-i) pattern */
SELECT REGEXP_INSTR('ABC','(?-i)b');
Result: 0

/* forces case-insensitive search */
SELECT REGEXP_INSTR('ABC' COLLATE utf8_bin,'(?i)b');
Result: 2

/* forces case-insensitive search */
SELECT REGEXP_INSTR('ABC' COLLATE utf8_bin,'(?+i)b');
Result: 2

Example: Match on more than one alternative

Consider a database table called Employee with the following records:

The below query is used to get the location of first occurrence of vowel in the records of Name column. To specify more than one alternative in the pattern, | is used.

SELECT *, 
REGEXP_INSTR(Name, 'a|e|i|o|u') AS first_occurrence
FROM Employee

This will produce a result similar to:

Example: Matching on multiple characters

Consider the above discussed table. The below query is used to get the location of first occurrence of "ar" or "er" (whichever comes first) in the records of City column. To specify more than one alternative in the pattern, [  ] is used

SELECT *, 
REGEXP_INSTR(City, '[ae]r') AS first_occurrence
FROM Employee

This will produce a result similar to:

Example: Using with Multibyte character

Casting a Multibyte character set as BINARY is useful to get the offsets in bytes. See the example below:

SELECT REGEXP_INSTR('BJÖRN','N');
Result: 5

/* using BINARY to convert string to a binary string */
SELECT REGEXP_INSTR(BINARY 'BJÖRN','N');
Result: 6