For year’s I’ve used the WordPress Plugin SyntaxHighlighter to display code on my site. Yesterday I ran into a huge problem. I wanted to change the way code is displayed on my site, which required changing hundreds of tags. SytaxHighlighter uses bash tags in shortcode format, but I needed to change all of those to standard pre tags. Not a fun night!
Every plugin a tried to do a search and replace didn’t work. I’m pretty sure the plugins weren’t able to handle the special characters properly. I hosed my database a few times along the way. Everything from screwed up Primary Key’s to bizarre Auto Increment problems. In the end, simplicity prevailed.
How To Search & Replace In MySQL
Most every website these days has PHPmyAdmin installed. If not, it’s easy to do so (I’ll write a post on that soon). This is precisely the tool to do this job quickly and correctly.
Go ahead and log into PHPmyAdmin and select your database in the left-hand column. Then select “SQL,” which is located in the top row of buttons.
Now, copy & paste (ctrl-v to paste) the following code into the white box, making sure to remove anything that might be in there already. Replace Item to replace here with the old word/tag/link, and Replacement text here with the new word/tag/link. Be sure to keep the ‘ on each side. Refer to the image below for a reference.
Code:
UPDATE wp_posts SET post_content = REPLACE ( post_content,'Item to replace here','Replacement text here');
Finally, click the Go button. It shouldn’t take more than a second or two. Once it’s finished you should see a confirmation letting you know how many rows were affected; this is how you know it was successful. If it says 0 rows affected, it means nothing was changed.
Repeat these steps for each word/link/tag you need to replace. I had to run it twice; once to replace the opening tag, and a second time to replace the closing tag. After, hundreds of posts had been updated and it only took a few minutes.
Conclusion
This is, by far, the best method to do a search and replace in a MySQL database. In addition to not having to install another plugin on your website, you can include special characters without having to worry about what’s going to happen to your database. If you need help or just want to say hi, feel free to leave a comment below! As always, don’t forget to do a backup of your database beforehand!
This was a huge help. Thank you.