Character Encoding Problems with Content

I recently worked on a large wordpress blog site that had character encoding problems with the content.  It’s not that big a deal to fix one or two instances where you see funny looking characters displayed on the page, but when you have hundreds of them, it’s time to turn to mysql queries to fix the problem.

These queries will make quick work of the pages, posts, custom post types, and comments.  There may still be some encoding problems if the content is stored in separate tables, like wp_options.

UPDATE wp_posts SET post_content = REPLACE(post_content, BINARY 'Â', '');
UPDATE wp_posts SET post_content = REPLACE(post_content, BINARY '“', '“');
UPDATE wp_posts SET post_content = REPLACE(post_content, BINARY '”', '”');
UPDATE wp_posts SET post_content = REPLACE(post_content, BINARY '’', '’');
UPDATE wp_posts SET post_content = REPLACE(post_content, BINARY '‘', '‘');
UPDATE wp_posts SET post_content = REPLACE(post_content, BINARY '—', '–');
UPDATE wp_posts SET post_content = REPLACE(post_content, BINARY '–', '—');
UPDATE wp_posts SET post_content = REPLACE(post_content, BINARY '•', '-');
UPDATE wp_posts SET post_content = REPLACE(post_content, BINARY '…', '…');

UPDATE wp_comments SET comment_content = REPLACE(comment_content, BINARY 'Â', '');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, BINARY '“', '“');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, BINARY '”', '”');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, BINARY '’', '’');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, BINARY '‘', '‘');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, BINARY '—', '–');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, BINARY '–', '—');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, BINARY '•', '-');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, BINARY '…', '…');

 

reference: http://digwp.com/2011/07/clean-up-weird-characters-in-database/