Replacing smart quotes, em-dashes, and ellipses with MySQL or PHP
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...
Thanks for this, I spent a long, long time trying to convert those stupid windows curly quotes. I really appreciate it.
hey thx man. i had some probs too, especially because the ellipsis. ^^ Thx!
By the way, this conversion operates faster on large tables with a WHERE clause (PostgreSQL):
UPDATE note
SET note_text = REPLACE(note_text, E'\…', E'\.\.\.')
WHERE note_text LIKE E'%\…%';
u r a CHAMP! Thank you sir!
Thank you! I have to accommodate numerous people entering product information from various irritating sources including MS Word. This is such a simple lifesaver!
This is awesome! Works like a charm.
Thanks so much
Cheers from South Africa
Thank you for this info!
One note..
I got an error in parsing the Ellipsis when using chr(133) in php.
(It changed all occurrences of norwegian "Å")
Php ord() gave 226 as the ellipsis and that worked well.
Best Regards
Eirik
Great solution, thanks for sharing
Thank you mate, great smart/curly quotes, em-dashes and ellipses reference!
Dude, you're AWESOME. This totally worked, couldn't find this info anywhere else.
We spend about 2 hrs figuring the problem. This worked like a charm.. Very good solution
Thank you! Customer has been driving me mad with this.
Thank you!, quickly fixed a knotty problem.