Search & Replace in MySQL (PHPMyAdmin)

Ever needed to search and replace text, URL’s or other data within a MySQL database?  Well it’s pretty easy.  Just be careful.  Make sure of everything before you commit.

Step 1, log in to your cPanel (or direct into PHPMyAdmin) and select your database

Step 2, Use the following syntax in the SQL area.

Step 3, press GO.

update [table_name] set [field_name] = replace([field_name],'[string_to_find]’,'[string_to_replace]’);

NOTE: the square brackets are NOT needed.  However the apostrophe’s are.

Example:

update wp_options set option_value = replace(option_value,’www.domain.com‘,’www.anotherdomain.com‘);

Usage is as follows:

‘table_name’ is the name of the table where the content you want to change resides

‘field_name’ is the name of the field inside the table where the content you want to change resides

‘unwanted_text’ is the text you want to find so that it can be replaced

‘wanted_text’ is the new text you want to insert

So for example let’s way you wanted to find every reference to “www.diskman.net” and replace it with “www.jamesdemetrie.com” within your database, you would firstly do a search in PHPMyAdmin and see where the references lie.  Once you know what table the data you want to replace is, you can then browse to see what field they are within.  Once you know what table and what field you can then apply the syntax above.

WARNING….  be careful, search first to see the results of your query before you commit the changes, and always backup prior to any database work.