How to fix strange characters in MySQL database

So, as a result of bad programming, my database got FILLED with strange characters like this:

 ’ … – “ †‘ 

These characters are representations of double quotes, single quotes, triple dots, etc… in different encoding formats.
I read that his happens when you copy lines from programs like Word, Wordpad, etc…
It has something to do with using latin-1 as the collation in your database. The many sites that talks about this issue recommends converting your table to use UTF-8.

Anyways, after hours of tedious research, I found a way to fix an existing table that has these weird characters in them.

The code is like this:

update <table_name>
set <field> = replace(<field>,'’','\'');
update <table_name>
set <field>= replace(<field>,'…','...');
update <table_name>
set <field>= replace(<field>,'–','-');
update <table_name>
set <field>= replace(<field>,'“','"');
update <table_name>
set <field>= replace(<field>,'”','"');
update <table_name>
set <field>= replace(<field>,'‘','\'');
update <table_name>
set <field>= replace(<field>,'•','-');
update <table_name>
set <field>= replace(<field>,'‡','c');

replace <table_name> with table name, and <field> with the field you are targeting.

This runs through the database and replaces all the strange characters with normal ones.

  • Thanks for this, handy!

    I squished this together into one query, it works just fine but I’m not sure which is best in terms of efficiency or best practice.

    update set

    =

    replace(replace(replace(
    replace(replace(replace(
    replace(replace(,

    '’', '\''),
    '…', '...'),
    '–', '-'),
    '“', '"'),
    '”', '"'),
    '‘', '\''),
    '•', '-'),
    '‡', 'c');

  • Woody NaDobhar

    You saved me a lot of time and boredom, my thanks.

  • Jim

    worked fro me

  • Paolo

    worked for me also

  • Yao

    Hey thanks so much, worked for me perfectly!

  • I’ve had a similar issue, but found that the culprit was actually utf8 vs latin1 character sets.

    Can you check the following post out and give me some feedback on wether or not this fixes your problem?

    http://blog.rvdavid.net/mysql-funnystrange-characters-replacing-apostrophes-after-import/

    Thanks in advance Vinh.

    rvdavid.

  • I was having same problems in my website, thats also worked for me.

    Thanks.

  • Gerrit

    Sweet, worked flawlessly for me.

  • n2fisher

    rvdavid lists a possible better solution (not yet tested) but the link is faulty .. he’s removed “blog” from it; the updated link is:

    http://www.rvdavid.net/mysql-funnystrange-characters-replacing-apostrophes-after-import/

  • bob

    hi all! not sure this will work with single and double curly quotes (e.g. ‘’ “”) … well it will replace them with non curly versions (” “”) which is fine when the output is simply a blob of text but not so great if it is a URL for example…

  • Ceusagaintp

    You are mi Idol!

  • Thanks, you helped out quite a bit with this post. I had around a ton of errors on an old site that had been exported/imported into multiple databases with little regard to character types.

    The issue has been nagging for over a year. Thanks for your help!

  • Big thanks for this – our phpbb forum move had gone a bit squiffy!

  • Ivan

    This is great! Saved me a ton of time. I also added:
    update set = replace(,’Â’,”);

  • thunderclap82

    This didn’t work for me. Now I have Â� showing up.

  • Test

    Works great :)

  • i was trying for an hour… didnt find any  solution.. finally got it here with google search… many thanks…

  • thanks! you saved me a lot of time. update wp_postmeta set meta_value = replace(meta_value,’£’,’£’) like a charm!

  • Very helpful, thanks! I doofed when moving a db and had some crazy character replacements like you outlined perfectly above