How to Reference Capture Groups in Regular Expressions in MySQL?

Regex Capture Group Referencing in MySQL

In MySQL, referencing a capture group within a regular expression can be achieved using the following method:

Syntax:

REGEXP_REPLACE(string, pattern, replacement)

For MySQL 8:

Capture groups can be created using parentheses (). To refer to a capture group, use $1, $2, and so on.

<code class="sql">SELECT REGEXP_REPLACE('stackoverflow','(.{5})(.*)','');
-- Output: "overflowstack"</code>

For MariaDB:

Capture groups in MariaDB are managed differently. Backreferences use \1, \2, and so forth.

<code class="sql">SELECT REGEXP_REPLACE('stackoverflow','(.{5})(.*)','\2\1');
-- Output: "overflowstack"</code>

Example:

The regular expression ^(.)1$ checks if there are two identical characters at the start of the string, but this syntax will not work in MySQL. Instead, use the following:

<code class="sql">SELECT REGEXP_REPLACE('aabbcc','^(.)(.)$','');
-- Output: "b"</code>

This regex matches the first two characters of the string (a and a) as two capture groups and retrieves the second character (b).

The above is the detailed content of How to Reference Capture Groups in Regular Expressions in MySQL?. For more information, please follow other related articles on the PHP Chinese website!