If your WordPress database is filled with weird characters (For example, if you pasted something from Microsoft Word…) the following recipe will solve the problem by replacing those characters by the correct characters.
Simply run the following SQL query on your WordPress database, using the command line client or PhpMyAdmin. This will remove weird characters from all your posts and comments.
Don't forget to backup your database before using this query.
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, '…', '…'); 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, '…', '…');
If you like to know more about WordPress SQL queries, you should have a look to this article.
Thanks to Jeff Starr for the cool tip!
Leave a Comment
WP Theme of the week
WPRecipes.com is hosted by VidaHost. Use our exclusive coupon CATSWHOCODE to get a 10% discount on hosting! 
On Twitter : Some @blackcatrescue friends r walking 4 Children's Hospital in #boston. Pls read Amanda's story http://t.co/npQ46UjEn9 #DT @blackcatrescue
Copyright © 2008 - 2013 WordPress Development by Jean-Baptiste Jung. Graphic art by GreenBubbles.org.ua, all right reserved.
10 Responses
Hi, I have issues be cause of some cheaters in my rss feed. Will this help on existing posts too? Can I run only theposts portion of this code?
Is there an easy way to do this for all sites in a WordPress multisite network? wp_posts or wp_comments work in a single site install, but in multisite it has to be wp_1_posts or wp_32_posts and so forth. When you have over 1000 sites, it would be nice not to have to try this one by one! Thanks.
I’ve seen this issue before, good to know there is an easy fix now!
I’d say the best way would be to export your entire DB using phpMyAdmin and replace the characters using a UTF-8 compatible editor like jEdit.
Coincidently this is a good way to port your DB to another server too (ie. local to production)
Very handy recipe. thanks Jean.
Do you know how much time this commands have saved me and will save me in the future? I always type my posts first in Word because of the timeout in PhpMyAdmin, but when I then copied my text to my wordpress, I always had to remove those weird characters in my database manually.
This is a real time saver!
+1 thanks.
Sorry if this question looks stupid, will it also remove our stored file in database?
When I tried to do what you suggested to remove the odd characters I got the following message:
SQL query:
UPDATE wp_posts SET post_content = REPLACE( post_content, ‘“’, ‘“’ ) ;
MySQL said:
#1146 – Table ‘adventuresofscatman_com_3.wp_posts’ doesn’t exist
Apart from copying from word etc, any idea why this usually happens? I.e. if I export a DB using phpMyAdmin on one server, and import it on the other using also phpMyAdmin there is a tendency that this happens…
This is a lifesaver! You wouldn’t believe how hard it is to find help with utf8 encoding in wordpress. I really appreciate the script!
Trackbacks: