How to Replace a String With a MySQL Query

By Lowell Heddings on June 26th, 2017

This is a really stupid article, and the only reason I’m writing it is in the hopes that I’ll finally stop forgetting the syntax and having to google which order the MySQL REPLACE function’s arguments are supposed to go. And then when I do try to look it up, I almost always end up on the documentation page for MySQL’s other REPLACE command…. which is used for updating a row in a table using the same syntax as an INSERT statement. Thanks for being confusing, MySQL!

All I’m trying to do is search and replace across my WordPress database with a simple query — for instance, when we switched HTG over to HTTPS, we had a ton of old links and images pointing to the HTTP version. So we had to update all <img src=””> links to be <img src=””>, and with nearly 20,000 post records in our database, that would require hiring a hundred interns. Or… a single SQL statement.

So now, hopefully marking the last time I ever look this up, the syntax for replacing a string using a MySQL query:

UPDATE table set column1=REPLACE(column1,'SEARCH','REPLACE');

For the purposes of our example, updating all links and images to HTTPS in WordPress, it ended up being:

UPDATE wp_posts set post_content = REPLACE(post_content, '','');

And with that, I saved the company from hiring 100 people to manually replace all of those links. I deserve a raise.

Are you a newb? If you don’t know how to open a MySQL prompt, well, you probably shouldn’t be reading this article. But just in case, you can open up a terminal and use the values in wp-config.php (assuming you are using WordPress) to connect to the database, and then paste in the SQL statement.

mysql -uUser -pPassword -hHost databasename

Have I mentioned that you probably should back up your WordPress database before you run this? And you should definitely test it on a development or QA environment? Otherwise you’re going to break everything. Don’t be that guy.