Replacing smart quotes, em-dashes, and ellipses with MySQL or PHP
Posted in Joomla, MySQL, PHP on March 5, 2009
13
Alternate title: "Help! My Quotes Appear as Question Marks or Other Strange Characters!"
The "Smart quotes" feature in Microsoft Office transforms straight quotes into curly quotes. It also transforms hyphens into em-dashes and three periods into ellipses. While one might think, "How lovely! My document looks almost as if I'm educated!" readers of said document may not. Microsoft, in its infinite wisdom, decided to assign special characters such as the ones I just mentioned to a range of codes above 128. Problem: these codes were already assigned to other characters, resulting in frustrating incompatibility with non-Microsoft systems.
Keep reading for some PHP and MySQL code to help out with this issue, as well as a Joomla! plugin.
Our introduction to this was in a situation where we had people using many different systems submitting articles to one of our programs. We decided that we wanted all our articles to use straight quotes, hyphens, and periods. This was partly for consistency, and partly because these characters are common to many character sets and won't cause incompatibilities.
This article isn't really intended as a complete explanation of charsets, though we recommend The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets if you're interested in learning more. We will however show you some MySQL and PHP techniques for replacing all instances of smart quotes, plus the en dash, em dash, and ellipsis with straight quotes, one or two dashes, or three dots. This code should operate with both the Windows-1252 charset, and also UTF-8, an encoding with an extended character set that has made it the preferred encoding for email and websites.
MySQL:
# First, replace UTF-8 characters. UPDATE `t` SET `c` = REPLACE(`c`, 0xE28098, "'"); UPDATE `t` SET `c` = REPLACE(`c`, 0xE28099, "'"); UPDATE `t` SET `c` = REPLACE(`c`, 0xE2809C, '"'); UPDATE `t` SET `c` = REPLACE(`c`, 0xE2809D, '"'); UPDATE `t` SET `c` = REPLACE(`c`, 0xE28093, '-'); UPDATE `t` SET `c` = REPLACE(`c`, 0xE28094, '--'); UPDATE `t` SET `c` = REPLACE(`c`, 0xE280A6, '...'); # Next, replace their Windows-1252 equivalents. UPDATE `t` SET `c` = REPLACE(`c`, char(145), "'"); UPDATE `t` SET `c` = REPLACE(`c`, char(146), "'"); UPDATE `t` SET `c` = REPLACE(`c`, char(147), '"'); UPDATE `t` SET `c` = REPLACE(`c`, char(148), '"'); UPDATE `t` SET `c` = REPLACE(`c`, char(150), '-'); UPDATE `t` SET `c` = REPLACE(`c`, char(151), '--'); UPDATE `t` SET `c` = REPLACE(`c`, char(133), '...');
// First, replace UTF-8 characters. $text = str_replace( array("\xe2\x80\x98", "\xe2\x80\x99", "\xe2\x80\x9c", "\xe2\x80\x9d", "\xe2\x80\x93", "\xe2\x80\x94", "\xe2\x80\xa6"), array("'", "'", '"', '"', '-', '--', '...'), $text); // Next, replace their Windows-1252 equivalents. $text = str_replace( array(chr(145), chr(146), chr(147), chr(148), chr(150), chr(151), chr(133)), array("'", "'", '"', '"', '-', '--', '...'), $text);
Joomla! plugin for removing smart/curly quotes, em-dashes and ellipses.
Additionally, here's a table of character codes that you may find useful:
| Character | HTML Code | Windows | UTF-8 | name |
|---|---|---|---|---|
| ‘ | ‘ | 145 | E28098 | left single curly quote |
| ’ | ’ | 146 | E28099 | right single curly quote |
| “ | “ | 147 | E2809C | left double curly quote |
| ” | ” | 148 | E2809D | right double curly quote |
| – | – | 150 | E28093 | en dash |
| — | — | 151 | E28094 | em dash |
| … | … | 133 | E280A6 | ellipsis |
Further reading from Wikipedia:
UTF-8
ISO/IEC 8859-1
Windows-1252







THANK YOU!!!!!!!
I spent 2 hours trying to figure this out. I was querying the jos_content table outside of Joomla, and it was a mess - as you can imagine.
VERY much appreciated - Rod
Thanks Rod! Glad to hear this helped you out
I also spent about 2 hours trying to figure this out. You're awesome Mango!
YOU ROCK Mango!
Thanks a LOT dude!!!! I was about to give up on trying to replace ellipses with triple dots till I came across your post. You be a life saver
- Ananth
very useful - and easily extended to include \x99 and other goofiness. Many thanks.
You are a god among men. I've spent all day trying to fix these dumb smart quotes and nowhere else on the web could I find your bit where you replace the following characters. This solved it. I owe you everything.
"\xe2\x80\x98", "\xe2\x80\x99", "\xe2\x80\x9c", "\xe2\x80\x9d", "\xe2\x80\x93", "\xe2\x80\x94", "\xe2\x80\xa6"
You guys be careful - you're boosting my already-inflated ego!
I am glad to hear this is helping.
love it, works fine for body but does it fix the MS word characters in the titles? It is only fixing the body of the articles for me and not the title? Any way to fix this??? If so, I will post positive comment in JED and our user forum at 4RSS.
4RSS
Latest version adds this feature.
Dude, thanks. I was about to tear out one of my three remaining hairs. This was simple, rocks and lets me get on with life.
If Microsoft was preventing you from getting on with your life I am very glad to have been able to help with that!
Welll... I was referring to my programming life...