MariaDB REGEXP_INSTR() Function

In MariaDB, the REGEXP_INSTR() function searches a string for a substring that matches the specified pattern and returns the position index of the substring.

MariaDB `REGEXP_INSTR()` Syntax

Here is the syntax for the MariaDB REGEXP_INSTR() function:

REGEXP_INSTR(str, pattern)

Parameters

str: Required. a string.
regexp: Required. regular expression.

Note that at the time of writing, MariaDB’s version accepts fewerparameters than MySQL REGEXP_INSTR(). MySQL REGEXP_INSTR() allows you to provide parameters for where to start the search, the number of occurrences to search for, the type of location to return, and modes used by the regular expression.

If you provide the wrong number of parameters, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'REGEXP_INSTR'.

Return value

The MariaDB REGEXP_INSTR() function searches a string str for matches the specified regexp pattern and returns the index of the substring. Returns NULL if there is no match.

REGEXP_INSTR() will return NULL if str or regexp is NULL.

MariaDB `REGEXP_INSTR()` Examples

Basic example

The following statement is used to find the first consecutive numbers in the string '123 abc 456 def'.

SELECT REGEXP_INSTR('123 abc 456 def', '\\d+');

Output:

+-----------------------------------------+
| REGEXP_INSTR('123 abc 456 def', '\\d+') |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+

No match

Here’s an example with no matches:

SELECT REGEXP_INSTR('123 abc 456 def', '\\d{5,}');

Output:

+--------------------------------------------+
| REGEXP_INSTR('123 abc 456 def', '\\d{5,}') |
+--------------------------------------------+
|                                          0 |
+--------------------------------------------+

In this example, the regex \\d{5,} represents a sequence of at least 5 consecutive digits, and such a substring does not exist in the original string, so it returns 0.

Conclusion