’ characters or similar on your WordPress site

By | August 16, 2013

If you see randomʉ۪ characters or similar on your WordPress site its likely to be an encoding mismatch between your WordPress installation and your MySQL database server.

I did the following to fix mine, your mileage may vary.

1. Confirm wp-config.php has the encoding set as UTF8..

/** Database Charset to use in creating database tables. */
define(‘DB_CHARSET’, ‘utf8’);

2. dump your database and keep it as a backup.

3. Update the encoding on all your tables within your database

# mysql -u Username -pPassword –database=dbname -B -N -e “SHOW TABLES” | awk ‘{print “ALTER TABLE”, $1, “CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;”}’ | mysql -u Username -pPassword –database=dbname

* Change Username, Password and dbname twice, in both parts of the mysql command. Note there is no space between -p and the Password.

4. Execute the following SQL commands

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,’’’, “’”);

5. Need to take a 2nd look into replacing these, but it’ll need regexp to make the “curly close quote” replace work.
replacements << [‘“’, ‘“’] # curly open quote
replacements << [/â€[[:cntrl:]]/, ‘”’] # curly close quote
* This was from http://markmcb.com/2011/11/07/replacing-ae%E2%80%9C-ae%E2%84%A2-aeoe-etc-with-utf-8-characters-in-ruby-on-rails/