Perform a regex replace in MySQL

Let’s assume you’re working on a WordPress database and you need to update the wp_posts.post_content column of various rows. More specifically, you want to replace all <a> tags that point to http://localhost:4000. Here is an approach you might take.

Back up your database

Use a GUI tool like TablePlus or use the command line.

Test your regex

Use regexr.com to ensure your regex correctly captures what you want and only what you want.

Test your regex with REGEXP_REPLACE using dummy input

Once you have a regex, test it out with the REGEXP_REPLACE function in a SQL console. For example, you might run:

SELECT REGEXP_REPLACE(
	'<h3 id="sources"><a href="http://localhost:4000/docs/computer-systems/what-is-gnu.html#sources"></a>Sources</h3>',
	'<a href="http:\/\/localhost:4000.*"><\/a>', 
	''
);

You should check the query result to make sure your regex works fine with MySQL.

Run query on a single row

Find a row that the query should work against and run it. For example:

UPDATE
	wp_posts
SET
	post_content = REGEXP_REPLACE(post_content, '<a href="http:\/\/localhost:4000.*"><\/a>', '')
WHERE
	id = 713;

Notice the WHERE id = 713 clause.

Run query against all rows

Once you’re satisfied that the regex is what you want, remove or adjust the WHERE clause to target all rows.