Skip to content

REGEXP_SUBSTR()

Function Description

REGEXP_SUBSTR() is used to return a substring in the string expr that matches the regular expression pattern pat.

grammar

> REGEXP_SUBSTR(expr, pat[, pos[, occurrence[, match_type]]])

Parameter definition

  • expr: This is the original string, where you look for matches.

  • pat: This is a regular expression pattern, and the function looks for strings that match this pattern.

  • 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 string to return to the matching. The default value is 1, which means that the string that matches the first time is returned. If this value is a non-zero value n, the matching string that occurs nth time will be returned.

  • 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_SUBSTR('1a 2b 3c', '[0-9]a');
+---------------------------------------+
| regexp_substr(1a 2b 3c, [0-9]a) |
+---------------------------------------+
| 1a |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT REGEXP_SUBSTR('Lend for land', '^C') Results;
+---------+
| Results |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)