Clean Up Weird Characters in Database
It’s been a crazy month, with lots of drama all over the place. Here at DigWP.com, we had an episode where the site was all screwed up and not loading or only partially loading, blank white pages, and the whole bit. During the process of keeping it together and trying to restore full functionality, numerous database imports and exports were performed under a variety of circumstance. During the rush, apparently the most recent database backup file was somehow uncompressed outside of MySQL before final import.
Several days later, that decompression/unzipping basically converted every quotation mark, em dash, en dash, ellipses and other special characters into some really ugly-looking codes.
Some common examples of weird charactersWhat are they?
I think what happened is that the restoration database that we ended up using had been opened in a file/text editor. It’s just a guess, and sort of irrelevant, but the text editor converted our UTF-8 characters into some other character set, like ISO-8859-1. So after restoration, we ended up with hundreds of these weird characters in the database – quotes, hyphens, dashes, and ellipses were all converted to Klingon:
“ = left quote = “
†= right quote = ”
‘ = left single quote = ‘
’ = right single quote = ’
— = en dash = –
– = em dash = —
• = hyphen = -
… = ellipsis = …Identifying most of these characters was relatively painless, but the en-dash and em-dash characters may be reversed (i.e., – = em dash, and — = en dash). Testing the other character replacements in the database was easy, but discerning between instances of em & en dashes proved futile. So do your own testing and make good backups before making any mass changes. Hopefully someone can help us out with more of the specifics.
Clean ’em up
Update: just found the remove_accents() function, which looks super useful for cleaning up “weird” characters in the database. </update>
Before making any changes to your database, make sure you have a good backup (or three). Then to clean up these weird characters from the WordPress database, use a program like phpMyAdmin to execute the following queries.
Clean up post_content
UPDATE wp_posts SET post_content = REPLACE(post_content, '“', '“');
UPDATE wp_posts SET post_content = REPLACE(post_content, 'â€', '”');
UPDATE wp_posts SET post_content = REPLACE(post_content, '’', '’');
UPDATE wp_posts SET post_content = REPLACE(post_content, '‘', '‘');
UPDATE wp_posts SET post_content = REPLACE(post_content, '—', '–');
UPDATE wp_posts SET post_content = REPLACE(post_content, '–', '—');
UPDATE wp_posts SET post_content = REPLACE(post_content, '•', '-');
UPDATE wp_posts SET post_content = REPLACE(post_content, '…', '…');Clean up comment_content
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '“', '“');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, 'â€', '”');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '’', '’');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '‘', '‘');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '—', '–');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '–', '—');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '•', '-');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '…', '…');Want more SQL recipes like this one? Check out Wizard’s SQL Recipes for WordPress — includes an entire chapter on optimizing the WP database!
Other tables
While cleaning up the DigWP database, several other weird characters also showed up in various places, but they were very few in number. I also noticed several instances of converted quotes, dashes, and hyphens scattered around in some other tables, mostly in the options table, buried deep within temporary rss_ data. So I didn’t bother with anything beyond the post_content and comment_content tables, but easily could have done so by modifying the previous queries like so:
UPDATE [table_name] SET [col_name] = REPLACE([col_name], '…', '…');
Just replace [table_name] with whatever table you want to clean up, col_name with the column name, and then replicate or edit the query with the proper character replacements.
Lesson learned
Take-home message: don’t open your database in a text editor. But if you do, execute these SQL queries for easy clean-up.
About the author
Jeff Starr is a professional web developer and book author with over 15 years of experience. Jeff works with WordPress every day, designing themes, developing plugins, and securing sites. His books include Digging Into WordPress, WordPress Themes In Depth, and The Tao of WordPress.
20 responses
-
FrankSee the plugin or standalone program Adminer for this job, its very easy and inside the backend of WP, and also very fast and light. Also you can use a plugin liek Search & Replace for this jop – maybe its solutions a little bit easier for many users.
-
Jeff Starr Post AuthorSweet tips – Thanks for sharing :)
-
Frank
welcome and also thanks for sharing your content.
-
-
-
This happened to me fairly recently when I moved my website. I wish I would have had this information (or realized what had happened) then, but I’m glad I found it now for future reference. Thanks for putting it together!
-
Cory HowellThis is awesome… I have 2-3 sites where the characters seem to get mixed up on a regular basis. This will be an easy fix & hopefully it will be a one-timer for mine.
-
Phil SherryWould it not be better to just chuck entities in there?
eg:
REPLACE(post_content, '…', '…')-
Jeff Starr Post Author
Yes, this is an excellent idea. I actually did replace the em/en dashes with HTML equivalents during the procedure. Not everyone uses them, so I went with UTF-8 entities for the lot of ’em.
-
-
Phil Sherry
Dang, my entity echo’d out.
eg:
REPLACE(post_content, '…', '& hellip ;') -
matt mcinvaleif you’re running mysqldump from the command line, make sure you use the -r switch rather than redirecting output to a file.
-
matt mcinvale
this comment is here because i failed to hit the subscribe to comment checkbox below.
-
-
Chuck ReynoldsThat would make a great little plugin… justsayin
-
ChrisThere’s a great plugin called Search Replace that can accomplish the same task for those who are hesitant to mess with the database directly.
-
lawlessI ran into the same problem with a migration from mySQL 4 to mySQL 5. For some reason all the special characters got messed up in the migration, likely for the same character encoding issue.
This is a useful SQL snippet to keep around for future reference though.
-
JimmyA small little typo at the last bit there :-
UPDATE [table_name] SET [table_name] = REPLACE([table_name], '…', '…');Should be
UPDATE [table_name] SET [col_name] = REPLACE([col_name], '…', '…');-
Jeff Starr Post Author
Fixed! – Thank you, Jimmy :)
-
-
StephanieWhat an odd situation to have. I’ve tried to Search Replace plugin and that does a lot of good. I used it to replace a bunch of links that were either updated or removed. If you know a way to prevent weird characters from messing up the database, I look forward to it. I’m sure it’ll be something with html entities and converting them beforehand.
-
RichardHi
I (actually helping a friend with this problem) have a similar issue but it is bad character encoding with a plugin/text widget. This can be seen online @exploreinternetmarketingonline.
How it should look like can be seen @onlinewpthemetest?
I am grateful for any advice on how to fix this please! Thanks
-
ShaolinTigerFantastic stuff, very useful.
Just two small tips/etc:
1) This code WILL NOT work if pasted into a MySQL terminal session via PuTTY etc – it works perfectly in phpMyAdmin though.
2) I found a lot in the titles of posts too – so I did the same set of queries for post_title as well.
-
Stephen R. SmithThis is extremely handy. I’ve been procrastinating fixing all the old posts that were messed up when our old hosting provider rev’d MySQL without warning.
Thanks for this – great time saver!
-
Mukesh Kumar replace with ?????????
