How to Reference Capture Groups in MySQL\'s Regular Expressions?

Referencing Capture Groups in MySQL's Regular Expressions

MySQL offers comprehensive regular expression support, including the ability to capture and reuse portions of a matched pattern. However, capturing and referencing groups differ slightly from other regex implementations.

In the example provided, REGEXP '^(.)1$' attempts to match a character repeated immediately after itself. Unfortunately, it won't work in MySQL as capturing groups are not supported natively.

Solution for MySQL 8

MySQL 8 introduces the REGEXP_REPLACE function, which supports capture groups. To reference a captured group, use $n, where n is the capture group's index (starting from 1). For instance:

SELECT REGEXP_REPLACE('stackoverflow','(.{5})(.*)','');
-- Result: "overflowstack"

In the above example, the first capture group ((.{5})) matches the first five characters (stack). The second capture group ((.*)) matches the remaining characters (overflow). They are then swapped in the replacement string.

Solution for MariaDB

MariaDB, a fork of MySQL, also supports capture groups in regex but with a different approach. It uses \n to reference capture groups in REGEXP_REPLACE:

SELECT REGEXP_REPLACE('stackoverflow','(.{5})(.*)','\2\1');
-- Result: "overflowstack"

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