MySQL REGEXP_INSTR() Function

In MySQL, the REGEXP_INSTR() function searches a string for a substring that match a specified regular expression and returns the index of the substring.

By default, the REGEXP_INSTR() function performs case-insensitive matching.

REGEXP_INSTR() Syntax

REGEXP_INSTR() Here is the syntax for MySQL :

REGEXP_INSTR(str, regexp)
REGEXP_INSTR(str, regexp, position)
REGEXP_INSTR(str, regexp, position, occurrence)
REGEXP_INSTR(str, regexp, position, occurrence, return_option)
REGEXP_INSTR(str, regexp, position, occurrence, return_option, mode)

Parameters

str
Required. The string to search.
regexp
Required. The Regular expression.
position
Optional. The starting position to start the search. The default is 1.
occurrence
Optional. The first number of matches. The default is 1.
return_option
Optional. It indicates which positional index to return. Returns the position index of the first character of the matched substring if it is 0; Returns the position index of the following character of the matched substring if it is 1. The default is 0.
mode
Optional. The match mode. It indicates how to perform matching.

mode can be one or more values in the followings:

  • c: Case-sensitive
  • i: Case-insensitive
  • m: Multi-line match pattern
  • n: The dot . can match end of line
  • u: Unix line endings only

If there are conflicting options in mode, the rightmost one takes precedence.

Return value

The REGEXP_INSTR() function returns the index of a substring that matches the gave regular expression. This function returns NULL if there is no match.

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

REGEXP_INSTR() Examples

The following statement is used to get the index of the first substring that has only numbers in the string '123 abc 456 def'.

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

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

If you want to get the second substring that has only numbers, use the following statement:

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

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

If you want to get the index after the first matched substring in '123 abc 456 def', use the following statement:

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

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