vinhboy.com

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.