MySQL REGEXP_INSTR() Function

Summary: in this tutorial, you will learn how to use the MySQL REGEXP_INSTR() function to obtain the starting position of a substring that matches a regular expression within an input string.

Introduction to MySQL REGEXP_INSTR() function

The REGEXP_INSTR() function returns the starting position of a substring that matches a regular expression pattern within a given string.

Here’s the basic syntax of the REGEX_INSTR() function:

REGEXP_INSTR(
   expression, 
   pattern, 
   position,
   occurrence,
   return_option,
   match_type
)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• expression: The input string where you want to search for the pattern.
• pattern: The regular expression pattern to match.
• position: The position in the expression at which the function starts the search. The default of the position is 1 which is at the beginning of the string.
• occurrence: This parameter determines which occurrence of a match to search for. It defaults to 1 if you omit it.
• return_option: return the position of the matched substring’s first character if return_option is 0. Return the position following the matched substring if the return_option is 1. The return_option defaults to 0.
• match_type: This is a string that instructs how the function should perform matching.

The match_type may contain any or all of the following characters that specify how the function performs matching:

If the expression or pattern is NULL, the REGEXP_INSTR() function returns NULL.

MySQL REGEXP_INSTR() function examples

Let’s take some examples of using the REGEXP_INSTR() function

1) Simple MySQL REGEXP_INSTR() function example

The following example uses the REGEXP_INSTR function to get the starting position of a sequence of digits:

SELECT 
  REGEXP_INSTR(
    '1936 Mercedes-Benz 500K Special Roadster', 
    '\\d+'
  ) position;Code language: SQL (Structured Query Language) (sql)

Output:

+----------+
| position |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The pattern \\d+ matches one or more digits. Therefore, the REGEX_INSTR function returns position 1 which is the starting position of the matched string 1936.

2) Position example

By default, the REGEX_INSTR() starts searching from the beginning of the string. However, you can change this behavior by specifying the position parameter.

For example, the following query uses the REGEXP_INSTR function to search for a sequence of digits starting at position 5:

SELECT 
  REGEXP_INSTR(
    '1936 Mercedes-Benz 500K Special Roadster', 
    '\\d+', 5
  ) position;Code language: SQL (Structured Query Language) (sql)

Output:

+----------+
| position |
+----------+
|       20 |
+----------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

It returns the position 20, which is the starting position of the substring 500.

3) Occurrence example

To find the position of a specific match, you can use the occurrence parameter. For example, the following query returns the positions of the first and second matches:

SELECT 
  REGEXP_INSTR(
    '1936 Mercedes-Benz 500K Special Roadster', 
    '\\d+', 1, 1
  ) first_match, 
  REGEXP_INSTR(
    '1936 Mercedes-Benz 500K Special Roadster', 
    '\\d+', 1, 2
  ) second_match;Code language: SQL (Structured Query Language) (sql)

Output:

+-------------+--------------+
| first_match | second_match |
+-------------+--------------+
|           1 |           20 |
+-------------+--------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

4) return_option example

By default, the starting position of the match is the beginning of the substring that matches the regular expression.

For example, REGEXP_INSTR returns 1 for the match 1936 because the first digit 1 in 1936 has the position of 1 in the input string.

If you want to get the position after the digit 6 of the substring, you can change the return_option to 1:

SELECT 
  REGEXP_INSTR(
    '1936 Mercedes-Benz 500K Special Roadster', 
    '\\d+', 1, 1, 1
  ) first_match;Code language: SQL (Structured Query Language) (sql)

Output:

+-------------+
| first_match |
+-------------+
|           5 |
+-------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

5) match_type example

By default, the REGEXP matches case-insensitively. If you want to match a pattern case-sensitively, you can use the match_type option 'c':

SELECT 
  REGEXP_INSTR(
    '1936 Mercedes-Benz 500K Special Roadster', 
    '\\d+k', 1, 1, 1, 'c'
  ) position;Code language: SQL (Structured Query Language) (sql)

Output:

+----------+
| position |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

In this example, the pattern \\d+k matches one or more digits followed by the letter k (lowercase).

But the match_type c instructs the REGEXP_INSTR() function to match case-sensitively, therefore, the function finds no match. As a result, it returns 0.

6) Using REGEXP_INSTR() function with table data

We’ll use the products table from the sample database:

The following query uses the REGEX_INSTR() function to find the position of the 4-digit substring that matches the pattern \\d{4}:

SELECT 
  productName 
FROM 
  products 
WHERE 
  REGEXP_INSTR(productName, '\\d{4}') > 0;Code language: SQL (Structured Query Language) (sql)

Here’s the partial output:

+---------------------------------------------+
| productName                                 |
+---------------------------------------------+
| 1969 Harley Davidson Ultimate Chopper       |
| 1952 Alpine Renault 1300                    |
| 1996 Moto Guzzi 1100i                       |
| 2003 Harley-Davidson Eagle Drag Bike        |
| 1972 Alfa Romeo GTA                         |
| 1962 LanciaA Delta 16V                      |
...Code language: SQL (Structured Query Language) (sql)

Summary

• Use the MySQL REGEXP_INSTR() function to get the position of a substring that matches a regular expression within a string.