MySQL REGEXP_SUBSTR() Function

In MySQL, The REGEXP_SUBSTR() function extracts a substring from a string that matches the specified regular expression and returns it.

By default, The REGEXP_SUBSTR() function performs case-insensitive matching.

REGEXP_SUBSTR() Syntax

Here is the syntax of MySQL REGEXP_SUBSTR() function:

REGEXP_SUBSTR(str, regexp)
REGEXP_SUBSTR(str, regexp, position)
REGEXP_SUBSTR(str, regexp, position, occurrence)
REGEXP_SUBSTR(str, regexp, position, occurrence, mode)

Parameters

str
Required. The string to get the substring from.
regexp
Required. The regular expression to which a substring to be matched.
position
Optional. The starting position to start the search. The default is 1.
occurrence
Optional. It indicates which occurrence of a match to search for. The default is 1.
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_SUBSTR() function extracts a substring from the string str that matches the specified regular expression regexp and returns it. If there is no match, return NULL.

if str or regexp is NULL, this function will return NULL.

REGEXP_SUBSTR() Examples

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

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

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

If you want to get the second substring that has only numbers in the string '123 abc 456 def', use the following statement:

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

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