| Title: | Technical Errata |
| URL: | http://www.rimmkaufman.com/rkgblog/2007/01/04/fixing-smart-quotes-in-wordpress/ |
| Printed: | March 20, 2010 |
| Source: | The Rimm-Kaufman Group Blog, info@rimmkaufman.com |
- January 4, 2007
- 5 comments
A mysql upgrade scrambled quote characters across this blog. This technical post explains the fix. Warning — off-topic post follows! Not a marketing post.
If you’re still reading at this point, perhaps you found this post via search and perhaps you’re trying to fix scrambled quote characters in your blog. Welcome. When we upgraded our mysql database powering this blog last week, the open and close single and double quote characters turned to mush. At first we though the issue was in the WordPress level. After discovering the problem was in the database, we found a helpful post by Derek Sivers over at ORielly’s OnLamp (Turning MySQL data in latin1 to utf8 utf-8) which provided the solution. First, make sure your problem is in your database, not in your WordPress. Second, confirm these hex encodings are correct for your situation by following Derek’s advice. And third, back up your database before issuing these updates as they’re irreversible. These queries worked for us:
/* fix open double quote */
update wp_posts set post_content=replace(post_content,
unhex('C3A2E282ACC593'), '"')
where post_content regexp unhex('C3A2E282ACC593');
/* fix close double quote */
update wp_posts set post_content=replace(post_content,
unhex('C3A2E282ACC29D'), '"')
where post_content regexp unhex('C3A2E282ACC29D');
/* fix open single quote */
update wp_posts set post_content=replace(post_content,
unhex('C3A2E282ACCB9C'), '\'')
where post_content regexp unhex('C3A2E282ACCB9C');
/* fix close single quote */
update wp_posts set post_content=replace(post_content,
unhex('C3A2E282ACE284A2'), '\'')
where post_content regexp unhex('C3A2E282ACE284A2');
The query stream we used to find Derek’s post:
- “wordpress quotes”
- “wordpress quotes smart”
- “strange quote character wordpress”
- “wordpress set bloginfo charset”
- “post charset wordpress”
- “wordpress charset”
- “malfunctioning quotes wordpress”
- “wptexturize”
- “wordpress character quotes in database”
- “mysql smart quotes”
- “mysql utf upgrade”
- “mysql quote characters upgrade”
If you like this post, consider subscribing to our RSS feed. You can also have new posts sent to you via email.
Related Posts
No related posts.
Thanks – I found I had a few more to do on one web, some more variations on quotes, dashes etc as the post title’s and names. I have put the SQL code on my web if anyone needs more. See http://www.anmari.com/anmari/2007/04/strange-characters/
Thanks!! In my case, I wondered if the problem was language encoding.
I’m not a fan of the smart-quote (“’”) appearing in an RSS entry at all, at least not for the next several years until technology generally improves around handling of encoding/fonts. It seems to be source of problems in the consumption and display of feeds, and isn’t necessary. The author, editor, or application could convert it to a single quote (“‘”) and no content would be lost.
Thanks for posting this. The post may be old, but the information was good and it REALLY helped when converting from one blog software to another.
Appreciate it!
There is a plugin for this, make sure you backup your database because its for old wordpress but it still works for me as of 2.8
http://wordpress.org/extend/plugins/utf-8-database-converter/