Search And Replace with MySQL

MySQL is a very powerful database management system with many useful features, there comes a time when you may need to search through a MySQL database and replace certain data which may now be out of date, the horrible thing is this data is repeated in many (and perhaps unknown) places throughout the table… So what do you do?…

Luckily there is some very simple SQL you can run on the database to find and replace that pesky outdated data with something new and fresh (and correct). For this example we will be using the REPLACE() function in MySQL, REPLACE() is described as a MySQL function that will find all occurrences of From_String and replace those with To_String where match is case-sensitive, you can either replace text on the fly or specify columns within the SQL to use for replacing data, most notably in SELECT and UPDATE statements, the syntax for REPLACE() is:

REPLACE(text_string, from_string, to_string);

So propose you have the following table:

Here is an example where we will update hotmail.com to live.com, remember REPLACE() is case-sensitive!

UPDATE `table_name`
SET `Email` = REPLACE(Email, 'hotmail.com', 'live.com');

Now if you are looking for a case-insensitive find and replace you can use this method which utilizes the LOWER() function in MySQL:

UPDATE `table_name`
SET `Email` = REPLACE(LOWER(Email), 'hotmail.com', 'live.com');

And yes it is that simple! Using the above as example you can easily update all that redundant data in your MySQL database!

Author: Dean WilliamsI'm a Web Developer, Graphics Designer and Gamer, this is my personal site which provides PHP programming advice, hints and tips

Twitter Facebook Linkedin