MySQL Search and Replace with phpMyAdmin

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:

mysql-search-replace-query-phpmyadmin

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.

1 thought on “MySQL Search and Replace with phpMyAdmin”

  1. Hi,

    Nice post! For this wonderful read, we present you with a unique opportunity of mystery shopping. Onion Insights Pvt. Ltd. is a Global Customer Experience Measurement (CEM) company that uses the tool of Mystery Shopping wherein one poses as a customer to a particular product or service and anonymously evaluates his/her shopping experience and provides a well-written structured report on the findings. The assignment lets you combine two of your favourite things – shopping and writing, you also get paid for shopping! You will be paid a nominal fee for your time and efforts.

Comments are closed.