How to Do Regular Expression Replace in MySQL

MySQL allows you to replace a single occurrence of substring using REPLACE() function. Sometimes you may need to replace multiple or all occurrence of substring, or even multiple matching strings with a given string. You can easily do this using regular expression replace. In this article, we will learn how to do regular expression replace in MySQL.

How to Do Regular Expression Replace in MySQL

If you are using MySQL 8+, you can directly use REGEXP_REPLACE. Here is the syntax for it.

REGEXP_REPLACE(col, regexp, replace)

Here is a simple example to replace parts of a literal string using regular expression.

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

You will get the following output.

over - stack - flow

For example, if you need to use this function in UPDATE statement, here is how to do this.

UPDATE `table_name` 
SET column_name_to_find=REGEXP_REPLACE(column_name_to_find, 'string-to-find', 'string-to-replace') 
WHERE column_name_to_find REGEXP 'string-to-find'

If you use MySQL<8, then you need to use a user-defined function that implements the logic for regex based string substitution. There are many readily available user defined packages with these kind of functions online. You can use mysql-udf-regexp package for this purpose. It provides the following 4 functions, similar to the ones available in Oracle.

REGEXP_LIKE(text, pattern [, mode])
REGEXP_SUBSTR(text, pattern [,position [,occurence [,mode]]])
REGEXP_INSTR?(text, pattern [,position [,occurence [,return_end [,mode]]]])
REGEXP_REPLACE?(text, pattern, replace [,position [,occurence [,return_end [,mode]]])

In this article, we have learnt how to use regular expressions to replace strings and substrings in MySQL

Also read:

How to Get Digits from String in MySQL
How to Use SCP with PEM SSH File
How to Ignore Certificate Errors in cURL
How to Get Creation Date of File & Folder in Linux
How to Delete Iptables Rules