This article reviews a quick way to do a search and replace within MySQL using phpMyAdmin.

I recently came across a problem in which I needed to change many items within a MySQL database at one time.  As I searched the Internet for a solution, I came across many unnecessary suggestions about downloading my database, making the changes offline, and then re-uploading my database.

All of this seemed a bit much.  I simply had to change the case (eg. from japan to Japan and many similar changes) and significant misspellings on several items.  Need I go through all of this?

Fortunately, it turned out that I needn’t!

The Solution to Search and Replace within MySQL using phpMyAdmin

The solution is very simple. Open up phpMyAdmin (usually through your hosting interface), choose the appropriate database (if you only have one, it will be selected already) click the SQL tab, and enter a command similar to this:

UPDATE 'countries' SET cname= replace(cname,"japan","Japan")

This fixed my problem with japan not being capitalized.

Let’s quickly look at the command. The first item, countries, is the table name. Cname is the field name, which as you can see is repeated as the first entry in the parenthesis. Following that, simply enter the item to be changed with the item you want it changed to.

It took much longer to find the solution than to implement. I hope that you quickly find this article so that you can quickly find out how to search and replace within your MySQL database using phpMyAdmin.

Ah, one more thing. If you found these instructions useful, help us all out by putting your examples in the comments below if you made a different type of modification. It will help out all who visit this page. The more examples the better!

Cheers,
Richard