When working with databases, the ability to find and replace MySQL data can save time and streamline bulk updates across your tables. A common scenario for using MySQL find and replace is updating outdated URLs or correcting typos in a large dataset without manually editing each entry.
Do you need to find and replace text in a MySQL database table? It’s easy to do when either running either inside PHP code, Java code, command line MySQL, MySQL Workbench, or PHPMyAdmin. First let’s cover the basics, let’s start with a simple find and replace:
UPDATE tablename SET column = 'new string' WHERE column = 'old string';
This is a basic MySQL search and replace query. It will do an exact match, so the following query will replace [email protected] to [email protected] in the Emails table:
UPDATE Emails SET Email = '[email protected]' WHERE column = '[email protected]';
An exact match means it will find any string equaling [email protected], but it won’t update [email protected]. If you want to update [email protected] no matter what comes before or after it, then you will use the MySQL replace function.
UPDATE tablename SET column = REPLACE(column, 'old string', 'new string')
What this will do is change any part of the string that has [email protected] to [email protected]. So [email protected] will become [email protected] with the following MySQL query:
UPDATE Emails SET Email = REPLACE(Email, '[email protected]', '[email protected]');
This will update the content throughout the entire table with that substring. Easy right? Let’s look at a more in depth example. Again, let’s say you want to update all occurrences of [email protected] to [email protected], no matter where it appears in a string, but only where a user’s last name is Smith. To do that, it would be:
UPDATE Emails SET Email = REPLACE(Email, '[email protected]', '[email protected]') WHERE last_name='Smith';
If you do not want to replace all occurrences, then you can set a LIMIT or more parameters to the WHERE clause like this:
UPDATE Emails SET Email = REPLACE(Email, '[email protected]', '[email protected]') WHERE last_name='Smith' AND first_name='John' LIMIT 3;
This will change the email address where the last name is Smith and the first name is John, but only in the first three instances it is found.
And that’s all there is to it. It is important to note that when searching using either of the above methods, that it is case sensitive. so [email protected] and [email protected] are two separate cases and only [email protected] will be replaced not the latter. We can get by this by using LOWER string function on the column, like so:
UPDATE Emails SET Email = REPLACE(LOWER(Email), '[email protected]', '[email protected]') WHERE last_name='Smith' AND first_name='John' LIMIT 3;
This will make the comparison lowercase, so that [email protected] will be seen as [email protected] when it is searching and then will be replaced with [email protected].
It is recommended that you make a backup of your table or even your whole database just in case something does go wrong and you need to restore it.
