Skip to content

REGEXP_INSTR()

Function Description

REGEXP_INSTR() Returns the starting position of the matched regular expression pattern in the string. If no match is found, the function returns 0.

grammar

> REGEXP_INSTR(expr, pat[, pos[, occurrence[, return_option[, match_type]]]])

Parameter definition

  • expr is the string to match.

  • pat is the regular expression to match in a string.

  • pos: This is an optional parameter that specifies where to start the search. The default value is 1, indicating that the search starts from the start position of the string.

  • occurrence: This is an optional parameter that specifies the location to which the match is returned. The default value is 1, indicating that the first match is returned.

  • return_option: This is an optional parameter that specifies whether the returned position is at the beginning or end of the pattern. If 0 or omitted, the function returns the position where the mode starts. If 1, the function returns the position after the end of the mode.

  • The match_type parameter is an optional string that specifies the way to match. This parameter can be composed of the following characters, each character specifies a matching method, and the order of characters does not affect the result:

    • `'c': case sensitive for matching (i.e. uppercase and lowercase letters are treated as different characters). By default, matches are case sensitive.
    • 'i': matches insensitively (i.e. uppercase and lowercase letters are treated as the same characters).
    • 'n': Allows the . symbol to match newlines. By default, the . symbol does not match newlines.
    • 'm': Treat a string as multiple lines. That is,^matches the beginning of the string or the beginning of any line, and$matches the end of the string or the end of any line. By default,^only matches the beginning of the string, and$` only matches the end of the string.
    • `'u': Treats the pattern as a UTF-8 string. By default, the pattern is treated as a byte string.

Example

mysql> SELECT REGEXP_INSTR('Hello, my number is 12345.', '[0-9]+');
+---------------------------------------------------------------------+
| regexp_instr(Hello, my number is 12345., [0-9]+) |
+---------------------------------------------------------------------+
| 21 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT REGEXP_INSTR('apple', 'z+');
+------------------------------+
| regexp_instr(apple, z+) |
+------------------------------+
| 0 |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT REGEXP_INSTR('Hello, World!', 'World');
+----------------------------------------+
| regexp_instr(Hello, World!, World) |
+----------------------------------------+
| 8 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT REGEXP_INSTR('Hello, World! World!', 'World', 1, 2);
+------------------------------------------------------------------+
| regexp_instr(Hello, World! World!, World, 1, 2) |
+------------------------------------------------------------------+
| 15 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)