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” by logging into phpmyadmin, then clicking on the Database, and then Search:
Enter the string,
And search for “All words”
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
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.
- Phpmyadmin>Search>Word(s) or value(s) to search for (wildcard: “%”): code
- Inside table(s): Select All: Go
- Browse (result)
- Scroll down, Query results operations > Export
- Exporting rows from “wp_5_posts ” table
- Custom – display all possible options
- Dump all rows
- Data dump options
- Function to use when dumping data: REPLACE
- Open the exported sql file in Notepad
- Search and Replace All:
- Search: searchstring
- Replace: targetstring
- 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.
Update: Alternate method using SQL:
Select the database in phpmyadmin.
Execute the following query:
UPDATE wp_5_posts SET `post_content` = REPLACE( `post_content`, '<pre class="lang:default decode:true ">', '<code>')
To see the search text, before a replacement, do:
SELECT * FROM `wp_5_posts` WHERE `post_content` LIKE '%<pre class="lang:default decode:true ">%'
Joel G Mathew, known in tech circles by the pseudonym Droidzone, is an opensource and programming enthusiast.
His favorite pastime is grappling with GNU compilers, discovering newer Linux secrets, writing scripts, hacking roms, and programs (nothing illegal), reading, blogging. and testing out the latest gadgets.
When away from the tech world, Dr Joel G. Mathew is a practising ENT Surgeon, busy with surgeries and clinical practise.