Replacing a string in a MySQL table

Today I’m going to tell you about a very simple trick to replace one text string with another in a field of a MySQL table. For example, when we migrate a site from one URL to another, sometimes certain links and images pointing to the previous site are left behind.

Replacing strings in WordPress

A simple trick, if you work with WordPress, is to use the plugin Better Search Replace, which searches for the string in the tables you specify and replaces it with the new string, and also lets you first emulate the change, to know how many changes will be made, and then run the change for real against the database. There is also a paid version that additionally lets you make backups before performing the change and offers some extra functions.

Replacing strings directly in MySQL

But if you don’t have WordPress, or you want to make the change in the MySQL table manually yourself, it’s as simple as using the REPLACE function that this database provides. So, we would run the following example query:

update `tabla-a-modificar` set campo-a-revisar=REPLACE(campo-a-revisar, ‘cadena-antigua’,’nueva-cadena’)

and with this, MySQL will change everywhere it finds in the “campo-a-revisar” field of the “tabla-a-modificar” table the string “cadena-antigua” for the string “nueva-cadena”.

As you can see, a very simple function that will save you many headaches when managing changes in MySQL tables. That said, before using these functions or plugins, I recommend that you make a backup of the database, just in case the change doesn’t go as expected and we make a real mess 🙂