+-----------------------------------+ mysql> SELECT REGEXP_REPLACE(’abc def ghi’, ’[a-z]+’, ’X’, 1, 3); +----------------------------------------------------+ | REGEXP_REPLACE(’abc def ghi’, ’[a-z]+’, ’X’, 1, 3) | +----------------------------------------------------+ | abc def X | +----------------------------------------------------+ 

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

Returns the substring of the string expr  that matches the regular expression specified by the pattern pat , NULL  if there is no match. If expr  or pat  is NULL , the return value is NULL . 

REGEXP_SUBSTR()  takes these optional arguments: 

• pos : The position in expr  at which to start the search. If omitted, the default is 1. 

• occurrence : Which occurrence of a match to search for. If omitted, the default is 1. 

• match_type : A string that specifies how to perform matching. The meaning is as described for REGEXP_LIKE() . 

Prior to MySQL 8.0.17, the result returned by this function used the UTF-16  character set; in MySQL 8.0.17 and later, the character set and collation of the expression searched for matches is used. (Bug #94203, Bug #29308212) 

For additional information about how matching occurs, see the description for REGEXP_LIKE() . 

## Regular Expression Syntax 

mysql> SELECT REGEXP_SUBSTR(’abc def ghi’, ’[a-z]+’); +----------------------------------------+ | REGEXP_SUBSTR(’abc def ghi’, ’[a-z]+’) | +----------------------------------------+ | abc | +----------------------------------------+ mysql> SELECT REGEXP_SUBSTR(’abc def ghi’, ’[a-z]+’, 1, 3); +----------------------------------------------+ | REGEXP_SUBSTR(’abc def ghi’, ’[a-z]+’, 1, 3) | +----------------------------------------------+ | ghi | +----------------------------------------------+ 

A regular expression describes a set of strings. The simplest regular expression is one that has no special characters in it. For example, the regular expression hello  matches hello  and nothing else. 

Nontrivial regular expressions use certain special constructs so that they can match more than one string. For example, the regular expression hello|world  contains the |  alternation operator and matches either the hello  or world . 

As a more complex example, the regular expression B[an]*s  matches any of the strings Bananas , Baaaaas , Bs , and any other string starting with a B , ending with an s , and containing any number of a  or n characters in between. 

The following list covers some of the basic special characters and constructs that can be used in regular expressions. For information about the full regular expression syntax supported by the ICU library used to implement regular expression support, visit the International Components for Unicode website. 

• ^ 

Match the beginning of a string. 

