Search and replace MySQL databases (Search within a field and replace a part of the string)

This is Part 2 of the article on MySQL string search and replace. This deals with searching for a sub string within a larger string in a field, update that string, and then put the large string back in the field. This is illustrated by a typical problem-How to replace HTML code tags by other tags within multiple posts in a WordPress install.

Changing HTML code tags throughout all posts in a WordPress installation.

I wanted to change the tag “code” to that of a syntax highlighter plugin I recently installed, to make my blog look pretty

First, I had to find out which table prefix was used by my site among the various multisite installs that I had.

So, go to the Database in Phpmyadmin, search for “blog.droidzone.in”

I found 7 matches inside table wp_5_options

Entering that Table, I found that the field siteurl, in table wp_5_options contained this URL. So it was evident that the tables with prefix “wp_5” contained the posts that I needed to modify.

I could then do a search within the whole database for the code tag, and then narrow down the results by those tables which had prefix wp_5.

Eureka! I found 107 matches inside table wp_5_posts for the tag.

Next I browse the results to see the field that contains this result.

I found that post_content is the field which contains posts.

So, my strategy is clear. I simply need to replace all strings which contain the code tag with the new tag.

I cant do a Mysql query on this, because I dont know how 😉

So, I export the rows containing these strings to an sql file, search and replace the tag in a text editor, and import the new sql file! 🙂

Caution: Dont choose the “Check all” followed by export! Only the first page of results will be exported in that case! You need to export everything, right? 🙂

So, choose the Export button under Query results operations, and export to sql file, then search and replace all code tags, by the target code tag.

The important thing to do before importing the results back is that the records already exist in the table so import will fail if simply imported. Instead, we need to replace the SQL statement “INSERT INTO” with the statement “REPLACE INTO ” (There is an alternate easier method-See below).

So, the following line:

INSERT INTO `wp_5_posts` (`ID`, `post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`, `post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`, `post_mime_type`, `comment_count`) VALUES

becomes:

REPLACE INTO `wp_5_posts` (`ID`, `post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`, `post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`, `post_mime_type`, `comment_count`) VALUES

Alternately, we can choose to use the REPLACE INTO statement instead of the INSERT INTO statement into the exported sql file directly from Phpmyadmin.

Summary,

1. Phpmyadmin>Search>Word(s) or value(s) to search for (wildcard: “%”): code
2. Inside table(s): Select All: Go
3. Browse (result)
4. Scroll down, Query results operations > Export
5. Exporting rows from “wp_5_posts ” table
6. Custom – display all possible options
7. Dump all rows
8. Data dump options
9. Function to use when dumping data: REPLACE
10. Open the exported sql file in Notepad
11. Search and Replace All:
12. Search: searchstring
13. Replace:  targetstring
14. Import sql file back

Voila, it’s done.

Of course SQL gurus may have easier methods to do the same. However this works for me fine. 😀

When everything is done, we simply import the file into the Table.