# **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**

```text
> 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**

```text
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)
```