MySQL Search and Replace with phpMyAdminby Team Goelji on May 10, 2010
If sometimes you mess up some characters in your database (wrong character encoding) or you want to change certain text in a certain table’s field, you can use below help a lot.
As you know that I use WordPress as my CMS choice. Many times when i finish up my work and if a client at that point ask to change (or i understood something wrong) some text that appeared many times in the site, it makes no sense to go to every post & do edit. So, here is small tip that can save you hours of work which I tested on MySQL5, PHP5 and phpMyAdmin 2.11.4.
Note: Before running any search and replace on your database, make a backup of your database, or you could loose your valuable data.
To search and replace a text string lets start step by step:
1. Logon to phpMyAdmin and click on your database name in left bar
2. Click the database name on which you want to run the search and replace query
3. In right hand, at the top of the window, click on the “SQL” tab. A window should pop-up like this:
4. In the text box, enter the following code. This is the generic setup, so edit to satisfy your needs:
UPDATE tablename SET tablefield = replace(tablefield, “SearchThis”, “ReplaceThis”);
for example when I used it on WordPress, it was:
UPDATE wp_posts SET <post_content = replace (post_content,'goyalg','goelji' );
You can also use the ‘WHERE’ statement, if you like. I will post the help about that soon.