How Can I Perform Regular Expression Replacements in MySQL?

Performing Regular Expression Replace in MySQL

Looking to modify data in your MySQL database using regular expressions? This question explores the availability of such functionality in MySQL and provides a solution for those using MariaDB or MySQL 8.0.

Regular Expression Replace Function

In MariaDB or MySQL 8.0, the REGEXP_REPLACE() function provides the ability to perform regular expression-based replacements within a database column. Its syntax is as follows:

REGEXP_REPLACE(col, regexp, replace)

where:

• col is the column containing the data to be modified
• regexp is the regular expression pattern to match
• replace is the replacement string

Using the Function

To utilize the REGEXP_REPLACE() function, simply include it in your query as shown:

SELECT REGEXP_REPLACE('stackowerflow', 'ower', 'over');

This will produce the output:

stackoverflow

Grouping in Regular Expressions

The function supports grouping within regular expressions, allowing for more complex replacements. For instance, the following statement uses grouping to swap the position of two matched words:

SELECT REGEXP_REPLACE("stackoverflow", "(stack)(over)(flow)", '\2 - \1 - \3')

This will return:

over - stack - flow

Alternative Approaches

For users not running MariaDB or MySQL 8.0, an alternative approach involving PHP and MySQL can be employed. This method involves selecting the data, using PHP for regular expression replacement, and then updating the database. However, it can be more cumbersome and performance-intensive than using the dedicated REGEXP_REPLACE() function.

The above is the detailed content of How Can I Perform Regular Expression Replacements in MySQL?. For more information, please follow other related articles on the PHP Chinese website!