If you need to replace a string in a MySQL query, there are a few different ways to do it. The simplest way is to use the REPLACE function. For example, to replace the string “foo” with the value “bar,” you could use the following query: SELECT REPLACE(REPLACE(REPLACE(foo,‘bar’,‘baz’),’ ‘,’’),’ ‘,’’) as replacement_string; The REPLACE function takes two arguments: the first is the string that you want to replace, and the second is a pattern for how to match that string. In this case, we’re using a pattern of “bar,” so all occurrences of “foo” will be replaced with “bar.” ..
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 links to be
, 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:
For the purposes of our example, updating all links and images to HTTPS in WordPress, it ended up being:
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.
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.
