When I try to restore a database dump from my production server to my development site, Unicode characters are getting munged. In production, I see "Björn" but when the same database is loaded locally I get "Björn"

I checked the dump file by loading it into vim and validating that it was UTF8 encoded (:set fenc) and that I could see "Björn". So I think the dump file is properly encoded.

All of the CREATE TABLE statements in the dump include DEFAULT CHARSET=utf8.

I checked the record in question with the mysql command line client against my development database, after I had restored the dump, with SELECT * FROM comments WHERE cid=... and I see "Björn". So I think the restore is good too.

The status report on both boxes shows the Unicode library as green: "PHP Mbstring Extension".

Which leaves...what, exactly? I'm stumped! I get very nervous when I can't easily restore my production backups. Can anyone throw me a clue?

In case it matters, my dev box is Mac OS X, Drupal 6.9, MySQL 5.0.27, PHP 5.2.6. My production box is running Linux, MySQL 5.0.32, PHP 4.4.6.

Many thanks!

Comments

starkos’s picture

Allow me one ping to catch some west coast US traffic. I've continued to look into it, but am still completely stumped.

John Yates’s picture

I'm experiencing the same problem - the hosting company promises to get back in 48 hrs.

I noticed different results from this query on the development site and the live site:

SHOW VARIABLES LIKE 'char%';

(live)
Variable_name Value
character_set_client: latin1
character_set_connection: latin1
character_set_database: latin1
character_set_filesystem: binary
character_set_results: latin1
character_set_server: latin1
character_set_system: utf8
character_sets_dir: /usr/local/mysql-5.0.67-linux-i686/share/mysql/charsets

(development)
Variable_name Value
character_set_client: utf8
character_set_connection: utf8
character_set_database: latin1
character_set_filesystem: binary
character_set_results: utf8
character_set_server: latin1
character_set_system: utf8
character_sets_dir: /usr/share/mysql/charsets/

I suspect the sql server configuration is mangling the character encodings. No idea though as to how to fix it.

starkos’s picture

I had to add the argument --default-character-set=utf8 to the mysql command I used to restore the dump (even though the dump file was UTF8 encoded, and even though every CREATE TABLE statement has a CHARSET=utf8).

Hope that helps you too!

John Yates’s picture

was to switch web hosts ;-)
I couldn't have added that argument even if I knew about it due to the crippled version of phpmyadmin provided. The tech support guys were no help at all. My advice to people looking for web hosting: stay away from Sasktel.

marthinal’s picture

I had exactly the same situation into a rds instance from amazon i fixed it changing db parameters using the api.

Its very important if someone have a csv file(utf8) and try to load data into a table also in utf8, that uses set names utf8 and check using a select to test the result. if you have chars not correctly translated please use SHOW VARIABLES LIKE 'char%'; to check the values, you need all utf8.

The only source of knowledge is experience. ~ Albert Einstein.

bdimaggio’s picture

...but I discovered that I had this problem too, only after my client had done a fair amount of work on the site. This meant that restoring my old copy of the database wasn't going to work, since that copy didn't contain the client's new nodes. My only option, that I knew of, was to search and replace the garbage characters. I developed this collection of Drupal-specific SQL statements and ran them all in the "SQL" tab of PHPMyAdmin, and they've fixed the problem for me:

/*
ASSUMPTIONS
’ > ’
– > –
— > —
“ > “
†> ”
”¢ > •
¿ > ¿
á > á
ó > ó
ñ > ñ
ň > ñ		<- why are there 2 garbage chars that both seem to point to ñ? I do not know.
é > é
ú > ú
à > í
 > [nothing]

TABLES/COLS TO WORK ON
node_revisions
  title
  body
  teaser
    
menu
  title
*/

UPDATE node_revisions SET title = REPLACE(title,'’', '’');
UPDATE node_revisions SET title = REPLACE(title,'–', '–');
UPDATE node_revisions SET title = REPLACE(title,'—', '—');
UPDATE node_revisions SET title = REPLACE(title,'“', '“');
UPDATE node_revisions SET title = REPLACE(title,'â€', '”');
UPDATE node_revisions SET title = REPLACE(title,'¿', '¿');
UPDATE node_revisions SET title = REPLACE(title,'á', 'á');
UPDATE node_revisions SET title = REPLACE(title,'ó', 'ó');
UPDATE node_revisions SET title = REPLACE(title,'ñ', 'ñ');
UPDATE node_revisions SET title = REPLACE(title,'ň', 'ñ');
UPDATE node_revisions SET title = REPLACE(title,'é', 'é');
UPDATE node_revisions SET title = REPLACE(title,'ú', 'ú');
UPDATE node_revisions SET title = REPLACE(title,'Ã', 'í');
UPDATE node_revisions SET title = REPLACE(title,'Â', ' ');

UPDATE node_revisions SET body = REPLACE(body,'’', '’');
UPDATE node_revisions SET body = REPLACE(body,'–', '–');
UPDATE node_revisions SET body = REPLACE(body,'—', '—');
UPDATE node_revisions SET body = REPLACE(body,'“', '“');
UPDATE node_revisions SET body = REPLACE(body,'â€', '”');
UPDATE node_revisions SET body = REPLACE(body,'¿', '¿');
UPDATE node_revisions SET body = REPLACE(body,'á', 'á');
UPDATE node_revisions SET body = REPLACE(body,'ó', 'ó');
UPDATE node_revisions SET body = REPLACE(body,'ñ', 'ñ');
UPDATE node_revisions SET body = REPLACE(body,'ň', 'ñ');
UPDATE node_revisions SET body = REPLACE(body,'é', 'é');
UPDATE node_revisions SET body = REPLACE(body,'ú', 'ú');
UPDATE node_revisions SET body = REPLACE(body,'Ã', 'í');
UPDATE node_revisions SET body = REPLACE(body,'Â', ' ');

UPDATE node_revisions SET teaser = REPLACE(teaser,'’', '’');
UPDATE node_revisions SET teaser = REPLACE(teaser,'–', '–');
UPDATE node_revisions SET teaser = REPLACE(teaser,'—', '—');
UPDATE node_revisions SET teaser = REPLACE(teaser,'“', '“');
UPDATE node_revisions SET teaser = REPLACE(teaser,'â€', '”');
UPDATE node_revisions SET teaser = REPLACE(teaser,'¿', '¿');
UPDATE node_revisions SET teaser = REPLACE(teaser,'á', 'á');
UPDATE node_revisions SET teaser = REPLACE(teaser,'ó', 'ó');
UPDATE node_revisions SET teaser = REPLACE(teaser,'ñ', 'ñ');
UPDATE node_revisions SET teaser = REPLACE(teaser,'ň', 'ñ');
UPDATE node_revisions SET teaser = REPLACE(teaser,'é', 'é');
UPDATE node_revisions SET teaser = REPLACE(teaser,'ú', 'ú');
UPDATE node_revisions SET teaser = REPLACE(teaser,'Ã', 'í');
UPDATE node_revisions SET teaser = REPLACE(teaser,'Â', ' ');

UPDATE menu SET title = REPLACE(title,'’', '’');
UPDATE menu SET title = REPLACE(title,'–', '–');
UPDATE menu SET title = REPLACE(title,'—', '—');
UPDATE menu SET title = REPLACE(title,'“', '“');
UPDATE menu SET title = REPLACE(title,'â€', '”');
UPDATE menu SET title = REPLACE(title,'¿', '¿');
UPDATE menu SET title = REPLACE(title,'á', 'á');
UPDATE menu SET title = REPLACE(title,'ó', 'ó');
UPDATE menu SET title = REPLACE(title,'ñ', 'ñ');
UPDATE menu SET title = REPLACE(title,'ň', 'ñ');
UPDATE menu SET title = REPLACE(title,'é', 'é');
UPDATE menu SET title = REPLACE(title,'ú', 'ú');
UPDATE menu SET title = REPLACE(title,'Ã', 'í');
UPDATE menu SET title = REPLACE(title,'Â', ' ');
bdimaggio’s picture

You should do the same thing for these tables & columns, I discovered (the hard way):

term_data:
name

blocks:
title

boxes:
body

view_view:
page_title
page_header
page_empty
page_footer

davidw’s picture

I've been struggling with this for a week. I knew this way would work, but I could not get the character strings right. Frustrated would be an understatement.

Thank you
Thank you
Thank ’ !!!!!

bdimaggio’s picture

Glad to be of help :)

Mainly for my own reference, here's that set of SQL statements, updated for Drupal 6 (minus the Views stuff...too involved for the moment I'm afraid):

UPDATE node_revisions SET title = REPLACE(title,'â„¢', '&trade;');
UPDATE node_revisions SET title = REPLACE(title,'’', '’');
UPDATE node_revisions SET title = REPLACE(title,'–', '–');
UPDATE node_revisions SET title = REPLACE(title,'—', '—');
UPDATE node_revisions SET title = REPLACE(title,'“', '“');
UPDATE node_revisions SET title = REPLACE(title,'â€', '”');
UPDATE node_revisions SET title = REPLACE(title,'¿', '¿');
UPDATE node_revisions SET title = REPLACE(title,'á', 'á');
UPDATE node_revisions SET title = REPLACE(title,'ó', 'ó');
UPDATE node_revisions SET title = REPLACE(title,'ñ', 'ñ');
UPDATE node_revisions SET title = REPLACE(title,'ň', 'ñ');
UPDATE node_revisions SET title = REPLACE(title,'é', 'é');
UPDATE node_revisions SET title = REPLACE(title,'ú', 'ú');
UPDATE node_revisions SET title = REPLACE(title,'Ã', 'í');
UPDATE node_revisions SET title = REPLACE(title,'Â', ' ');


UPDATE node_revisions SET body = REPLACE(title,'â„¢', '&trade;');
UPDATE node_revisions SET body = REPLACE(body,'’', '’');
UPDATE node_revisions SET body = REPLACE(body,'–', '–');
UPDATE node_revisions SET body = REPLACE(body,'—', '—');
UPDATE node_revisions SET body = REPLACE(body,'“', '“');
UPDATE node_revisions SET body = REPLACE(body,'â€', '”');
UPDATE node_revisions SET body = REPLACE(body,'¿', '¿');
UPDATE node_revisions SET body = REPLACE(body,'á', 'á');
UPDATE node_revisions SET body = REPLACE(body,'ó', 'ó');
UPDATE node_revisions SET body = REPLACE(body,'ñ', 'ñ');
UPDATE node_revisions SET body = REPLACE(body,'ň', 'ñ');
UPDATE node_revisions SET body = REPLACE(body,'é', 'é');
UPDATE node_revisions SET body = REPLACE(body,'ú', 'ú');
UPDATE node_revisions SET body = REPLACE(body,'Ã', 'í');
UPDATE node_revisions SET body = REPLACE(body,'Â', ' ');

UPDATE node_revisions SET teaser = REPLACE(teaser,'â„¢', '&trade;');
UPDATE node_revisions SET teaser = REPLACE(teaser,'’', '’');
UPDATE node_revisions SET teaser = REPLACE(teaser,'–', '–');
UPDATE node_revisions SET teaser = REPLACE(teaser,'—', '—');
UPDATE node_revisions SET teaser = REPLACE(teaser,'“', '“');
UPDATE node_revisions SET teaser = REPLACE(teaser,'â€', '”');
UPDATE node_revisions SET teaser = REPLACE(teaser,'¿', '¿');
UPDATE node_revisions SET teaser = REPLACE(teaser,'á', 'á');
UPDATE node_revisions SET teaser = REPLACE(teaser,'ó', 'ó');
UPDATE node_revisions SET teaser = REPLACE(teaser,'ñ', 'ñ');
UPDATE node_revisions SET teaser = REPLACE(teaser,'ň', 'ñ');
UPDATE node_revisions SET teaser = REPLACE(teaser,'é', 'é');
UPDATE node_revisions SET teaser = REPLACE(teaser,'ú', 'ú');
UPDATE node_revisions SET teaser = REPLACE(teaser,'Ã', 'í');
UPDATE node_revisions SET teaser = REPLACE(teaser,'Â', ' ');

UPDATE boxes SET body = REPLACE(body,'â„¢', '&trade;');
UPDATE boxes SET body = REPLACE(body,'’', '’');
UPDATE boxes SET body = REPLACE(body,'–', '–');
UPDATE boxes SET body = REPLACE(body,'—', '—');
UPDATE boxes SET body = REPLACE(body,'“', '“');
UPDATE boxes SET body = REPLACE(body,'â€', '”');
UPDATE boxes SET body = REPLACE(body,'¿', '¿');
UPDATE boxes SET body = REPLACE(body,'á', 'á');
UPDATE boxes SET body = REPLACE(body,'ó', 'ó');
UPDATE boxes SET body = REPLACE(body,'ñ', 'ñ');
UPDATE boxes SET body = REPLACE(body,'ň', 'ñ');
UPDATE boxes SET body = REPLACE(body,'é', 'é');
UPDATE boxes SET body = REPLACE(body,'ú', 'ú');
UPDATE boxes SET body = REPLACE(body,'Ã', 'í');
UPDATE boxes SET body = REPLACE(body,'Â', ' ');

UPDATE blocks SET title = REPLACE(title,'â„¢', '&trade;');
UPDATE blocks SET title = REPLACE(title,'’', '’');
UPDATE blocks SET title = REPLACE(title,'–', '–');
UPDATE blocks SET title = REPLACE(title,'—', '—');
UPDATE blocks SET title = REPLACE(title,'“', '“');
UPDATE blocks SET title = REPLACE(title,'â€', '”');
UPDATE blocks SET title = REPLACE(title,'¿', '¿');
UPDATE blocks SET title = REPLACE(title,'á', 'á');
UPDATE blocks SET title = REPLACE(title,'ó', 'ó');
UPDATE blocks SET title = REPLACE(title,'ñ', 'ñ');
UPDATE blocks SET title = REPLACE(title,'ň', 'ñ');
UPDATE blocks SET title = REPLACE(title,'é', 'é');
UPDATE blocks SET title = REPLACE(title,'ú', 'ú');
UPDATE blocks SET title = REPLACE(title,'Ã', 'í');
UPDATE blocks SET title = REPLACE(title,'Â', ' ');

UPDATE term_data SET name = REPLACE(name,'â„¢', '&trade;');
UPDATE term_data SET name = REPLACE(name,'’', '’');
UPDATE term_data SET name = REPLACE(name,'–', '–');
UPDATE term_data SET name = REPLACE(name,'—', '—');
UPDATE term_data SET name = REPLACE(name,'“', '“');
UPDATE term_data SET name = REPLACE(name,'â€', '”');
UPDATE term_data SET name = REPLACE(name,'¿', '¿');
UPDATE term_data SET name = REPLACE(name,'á', 'á');
UPDATE term_data SET name = REPLACE(name,'ó', 'ó');
UPDATE term_data SET name = REPLACE(name,'ñ', 'ñ');
UPDATE term_data SET name = REPLACE(name,'ň', 'ñ');
UPDATE term_data SET name = REPLACE(name,'é', 'é');
UPDATE term_data SET name = REPLACE(name,'ú', 'ú');
UPDATE term_data SET name = REPLACE(name,'Ã', 'í');
UPDATE term_data SET name = REPLACE(name,'Â', ' ');
clau_bolson’s picture

Thanks a lot, bdimaggio

In Drupal 6 you also have to

UPDATE node SET title = REPLACE(title,...

and replace

UPDATE menu SET title = REPLACE(title,...

with

UPDATE menu_links SET link_title = REPLACE(link_title,...

mattwmc’s picture

I need to replace those characters in the url (clean) as well.

What would be the proper term for that?

Thanks.

Edit: Figured it out.

UPDATE path_redirect SET source = REPLACE(source,'â„¢', '&trade;');
UPDATE path_redirect SET source = REPLACE(source,'’', '’');
UPDATE path_redirect SET source = REPLACE(source,'–', '–');
UPDATE path_redirect SET source = REPLACE(source,'—', '—');
UPDATE path_redirect SET source = REPLACE(source,'“', '“');
UPDATE path_redirect SET source = REPLACE(source,'â€', '”');
UPDATE path_redirect SET source = REPLACE(source,'¿', '¿');
UPDATE path_redirect SET source = REPLACE(source,'á', 'á');
UPDATE path_redirect SET source = REPLACE(source,'ó', 'ó');
UPDATE path_redirect SET source = REPLACE(source,'ñ', 'ñ');
UPDATE path_redirect SET source = REPLACE(source,'ň', 'ñ');
UPDATE path_redirect SET source = REPLACE(source,'é', 'é');
UPDATE path_redirect SET source = REPLACE(source,'ú', 'ú');
UPDATE path_redirect SET source = REPLACE(source,'Ã', 'í');
UPDATE path_redirect SET source = REPLACE(source,'Â', ' ');

UPDATE url_alias SET dst = REPLACE(dst,'â„¢', '&trade;');
UPDATE url_alias SET dst = REPLACE(dst,'’', '’');
UPDATE url_alias SET dst = REPLACE(dst,'–', '–');
UPDATE url_alias SET dst = REPLACE(dst,'—', '—');
UPDATE url_alias SET dst = REPLACE(dst,'“', '“');
UPDATE url_alias SET dst = REPLACE(dst,'â€', '”');
UPDATE url_alias SET dst = REPLACE(dst,'¿', '¿');
UPDATE url_alias SET dst = REPLACE(dst,'á', 'á');
UPDATE url_alias SET dst = REPLACE(dst,'ó', 'ó');
UPDATE url_alias SET dst = REPLACE(dst,'ñ', 'ñ');
UPDATE url_alias SET dst = REPLACE(dst,'ň', 'ñ');
UPDATE url_alias SET dst = REPLACE(dst,'é', 'é');
UPDATE url_alias SET dst = REPLACE(dst,'ú', 'ú');
UPDATE url_alias SET dst = REPLACE(dst,'Ã', 'í');
UPDATE url_alias SET dst = REPLACE(dst,'Â', ' ');

steveo1’s picture

This thread really helped me out! I also had lots of unicode characters and ended up making some modifications to the code here to work with D7.

/*
ASSUMPTIONS
’ > ’
– > –
— > —
“ > “
†> ”
”¢ > •
¿ > ¿
á > á
ó > ó
ñ > ñ
ň > ñ		<- why are there 2 garbage chars that both seem to point to ñ? I do not know.
é > é
ú > ú
à > í
 > [nothing]

TABLES/COLS TO WORK ON
node
   title
field_data_body
field_revision_body
   body_value
   body_summary
    
menu_links
  title
*/

UPDATE node SET title = REPLACE(title,'’', '’');
UPDATE node SET title = REPLACE(title,'–', '–');
UPDATE node SET title = REPLACE(title,'—', '—');
UPDATE node SET title = REPLACE(title,'“', '“');
UPDATE node SET title = REPLACE(title,'â€', '”');
UPDATE node SET title = REPLACE(title,'¿', '¿');
UPDATE node SET title = REPLACE(title,'á', 'á');
UPDATE node SET title = REPLACE(title,'ó', 'ó');
UPDATE node SET title = REPLACE(title,'ñ', 'ñ');
UPDATE node SET title = REPLACE(title,'ň', 'ñ');
UPDATE node SET title = REPLACE(title,'é', 'é');
UPDATE node SET title = REPLACE(title,'ú', 'ú');
UPDATE node SET title = REPLACE(title,'Ã', 'í');
UPDATE node SET title = REPLACE(title,'Â', ' ');

UPDATE node_revision SET title = REPLACE(title,'’', '’');
UPDATE node_revision SET title = REPLACE(title,'–', '–');
UPDATE node_revision SET title = REPLACE(title,'—', '—');
UPDATE node_revision SET title = REPLACE(title,'“', '“');
UPDATE node_revision SET title = REPLACE(title,'â€', '”');
UPDATE node_revision SET title = REPLACE(title,'¿', '¿');
UPDATE node_revision SET title = REPLACE(title,'á', 'á');
UPDATE node_revision SET title = REPLACE(title,'ó', 'ó');
UPDATE node_revision SET title = REPLACE(title,'ñ', 'ñ');
UPDATE node_revision SET title = REPLACE(title,'ň', 'ñ');
UPDATE node_revision SET title = REPLACE(title,'é', 'é');
UPDATE node_revision SET title = REPLACE(title,'ú', 'ú');
UPDATE node_revision SET title = REPLACE(title,'Ã', 'í');
UPDATE node_revision SET title = REPLACE(title,'Â', ' ');

UPDATE field_data_body SET body_value = REPLACE(body_value,'’', '’');
UPDATE field_data_body SET body_value = REPLACE(body_value,'–', '–');
UPDATE field_data_body SET body_value = REPLACE(body_value,'—', '—');
UPDATE field_data_body SET body_value = REPLACE(body_value,'“', '“');
UPDATE field_data_body SET body_value = REPLACE(body_value,'â€', '”');
UPDATE field_data_body SET body_value = REPLACE(body_value,'¿', '¿');
UPDATE field_data_body SET body_value = REPLACE(body_value,'á', 'á');
UPDATE field_data_body SET body_value = REPLACE(body_value,'ó', 'ó');
UPDATE field_data_body SET body_value = REPLACE(body_value,'ñ', 'ñ');
UPDATE field_data_body SET body_value = REPLACE(body_value,'ň', 'ñ');
UPDATE field_data_body SET body_value = REPLACE(body_value,'é', 'é');
UPDATE field_data_body SET body_value = REPLACE(body_value,'ú', 'ú');
UPDATE field_data_body SET body_value = REPLACE(body_value,'Ã', 'í');
UPDATE field_data_body SET body_value = REPLACE(body_value,'Â', ' ');

UPDATE field_data_body SET body_summary = REPLACE(body_summary,'’', '’');
UPDATE field_data_body SET body_summary = REPLACE(body_summary,'–', '–');
UPDATE field_data_body SET body_summary = REPLACE(body_summary,'—', '—');
UPDATE field_data_body SET body_summary = REPLACE(body_summary,'“', '“');
UPDATE field_data_body SET body_summary = REPLACE(body_summary,'â€', '”');
UPDATE field_data_body SET body_summary = REPLACE(body_summary,'¿', '¿');
UPDATE field_data_body SET body_summary = REPLACE(body_summary,'á', 'á');
UPDATE field_data_body SET body_summary = REPLACE(body_summary,'ó', 'ó');
UPDATE field_data_body SET body_summary = REPLACE(body_summary,'ñ', 'ñ');
UPDATE field_data_body SET body_summary = REPLACE(body_summary,'ň', 'ñ');
UPDATE field_data_body SET body_summary = REPLACE(body_summary,'é', 'é');
UPDATE field_data_body SET body_summary = REPLACE(body_summary,'ú', 'ú');
UPDATE field_data_body SET body_summary = REPLACE(body_summary,'Ã', 'í');
UPDATE field_data_body SET body_summary = REPLACE(body_summary,'Â', ' ');

UPDATE field_revision_body SET body_value = REPLACE(body_value,'’', '’');
UPDATE field_revision_body SET body_value = REPLACE(body_value,'–', '–');
UPDATE field_revision_body SET body_value = REPLACE(body_value,'—', '—');
UPDATE field_revision_body SET body_value = REPLACE(body_value,'“', '“');
UPDATE field_revision_body SET body_value = REPLACE(body_value,'â€', '”');
UPDATE field_revision_body SET body_value = REPLACE(body_value,'¿', '¿');
UPDATE field_revision_body SET body_value = REPLACE(body_value,'á', 'á');
UPDATE field_revision_body SET body_value = REPLACE(body_value,'ó', 'ó');
UPDATE field_revision_body SET body_value = REPLACE(body_value,'ñ', 'ñ');
UPDATE field_revision_body SET body_value = REPLACE(body_value,'ň', 'ñ');
UPDATE field_revision_body SET body_value = REPLACE(body_value,'é', 'é');
UPDATE field_revision_body SET body_value = REPLACE(body_value,'ú', 'ú');
UPDATE field_revision_body SET body_value = REPLACE(body_value,'Ã', 'í');
UPDATE field_revision_body SET body_value = REPLACE(body_value,'Â', ' ');

UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'’', '’');
UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'–', '–');
UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'—', '—');
UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'“', '“');
UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'â€', '”');
UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'¿', '¿');
UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'á', 'á');
UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'ó', 'ó');
UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'ñ', 'ñ');
UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'ň', 'ñ');
UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'é', 'é');
UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'ú', 'ú');
UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'Ã', 'í');
UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'Â', ' ');


UPDATE menu_links SET link_title = REPLACE(link_title,'’', '’') WHERE menu_name like '%main%';
UPDATE menu_links SET link_title = REPLACE(link_title,'–', '–') WHERE menu_name like '%main%';
UPDATE menu_links SET link_title = REPLACE(link_title,'—', '—') WHERE menu_name like '%main%';
UPDATE menu_links SET link_title = REPLACE(link_title,'“', '“') WHERE menu_name like '%main%';
UPDATE menu_links SET link_title = REPLACE(link_title,'â€', '”') WHERE menu_name like '%main%';
UPDATE menu_links SET link_title = REPLACE(link_title,'¿', '¿') WHERE menu_name like '%main%';
UPDATE menu_links SET link_title = REPLACE(link_title,'á', 'á') WHERE menu_name like '%main%';
UPDATE menu_links SET link_title = REPLACE(link_title,'ó', 'ó') WHERE menu_name like '%main%';
UPDATE menu_links SET link_title = REPLACE(link_title,'ñ', 'ñ') WHERE menu_name like '%main%';
UPDATE menu_links SET link_title = REPLACE(link_title,'ň', 'ñ') WHERE menu_name like '%main%';
UPDATE menu_links SET link_title = REPLACE(link_title,'é', 'é') WHERE menu_name like '%main%';
UPDATE menu_links SET link_title = REPLACE(link_title,'ú', 'ú') WHERE menu_name like '%main%';
UPDATE menu_links SET link_title = REPLACE(link_title,'Ã', 'í') WHERE menu_name like '%main%';
UPDATE menu_links SET link_title = REPLACE(link_title,'Â', ' ') WHERE menu_name like '%main%';
bdimaggio’s picture

Also for D7:

/*
ASSUMPTIONS
’ > ’
– > –
— > —
“ > “
†> ”
”¢ > •
¿ > ¿
á > á
ó > ó
ñ > ñ
ň > ñ
é > é
ú > ú
à > í
 > [nothing]
¯ > ï <- exciting new character for April 2013

TABLES/COLS TO WORK ON
node
   title
field_data_body
field_revision_body
   body_value
   body_summary
    
menu_links
  title
*/

UPDATE node SET title = REPLACE(title,'’', '’');
UPDATE node SET title = REPLACE(title,'–', '–');
UPDATE node SET title = REPLACE(title,'—', '—');
UPDATE node SET title = REPLACE(title,'“', '“');
UPDATE node SET title = REPLACE(title,'â€', '”');
UPDATE node SET title = REPLACE(title,'¿', '¿');
UPDATE node SET title = REPLACE(title,'á', 'á');
UPDATE node SET title = REPLACE(title,'ó', 'ó');
UPDATE node SET title = REPLACE(title,'ñ', 'ñ');
UPDATE node SET title = REPLACE(title,'ň', 'ñ');
UPDATE node SET title = REPLACE(title,'é', 'é');
UPDATE node SET title = REPLACE(title,'ú', 'ú');
UPDATE node SET title = REPLACE(title,'Ã', 'í');
UPDATE node SET title = REPLACE(title,'Â', ' ');
UPDATE node SET title = REPLACE(title,'¯', 'ï');

UPDATE node_revision SET title = REPLACE(title,'’', '’');
UPDATE node_revision SET title = REPLACE(title,'–', '–');
UPDATE node_revision SET title = REPLACE(title,'—', '—');
UPDATE node_revision SET title = REPLACE(title,'“', '“');
UPDATE node_revision SET title = REPLACE(title,'â€', '”');
UPDATE node_revision SET title = REPLACE(title,'¿', '¿');
UPDATE node_revision SET title = REPLACE(title,'á', 'á');
UPDATE node_revision SET title = REPLACE(title,'ó', 'ó');
UPDATE node_revision SET title = REPLACE(title,'ñ', 'ñ');
UPDATE node_revision SET title = REPLACE(title,'ň', 'ñ');
UPDATE node_revision SET title = REPLACE(title,'é', 'é');
UPDATE node_revision SET title = REPLACE(title,'ú', 'ú');
UPDATE node_revision SET title = REPLACE(title,'Ã', 'í');
UPDATE node_revision SET title = REPLACE(title,'Â', ' ');
UPDATE node_revision SET title = REPLACE(title,'¯', 'ï');

UPDATE field_data_body SET body_value = REPLACE(body_value,'’', '’');
UPDATE field_data_body SET body_value = REPLACE(body_value,'–', '–');
UPDATE field_data_body SET body_value = REPLACE(body_value,'—', '—');
UPDATE field_data_body SET body_value = REPLACE(body_value,'“', '“');
UPDATE field_data_body SET body_value = REPLACE(body_value,'â€', '”');
UPDATE field_data_body SET body_value = REPLACE(body_value,'¿', '¿');
UPDATE field_data_body SET body_value = REPLACE(body_value,'á', 'á');
UPDATE field_data_body SET body_value = REPLACE(body_value,'ó', 'ó');
UPDATE field_data_body SET body_value = REPLACE(body_value,'ñ', 'ñ');
UPDATE field_data_body SET body_value = REPLACE(body_value,'ň', 'ñ');
UPDATE field_data_body SET body_value = REPLACE(body_value,'é', 'é');
UPDATE field_data_body SET body_value = REPLACE(body_value,'ú', 'ú');
UPDATE field_data_body SET body_value = REPLACE(body_value,'Ã', 'í');
UPDATE field_data_body SET body_value = REPLACE(body_value,'Â', ' ');
UPDATE field_data_body SET body_value = REPLACE(body_value,'¯', 'ï');

UPDATE field_data_body SET body_summary = REPLACE(body_summary,'’', '’');
UPDATE field_data_body SET body_summary = REPLACE(body_summary,'–', '–');
UPDATE field_data_body SET body_summary = REPLACE(body_summary,'—', '—');
UPDATE field_data_body SET body_summary = REPLACE(body_summary,'“', '“');
UPDATE field_data_body SET body_summary = REPLACE(body_summary,'â€', '”');
UPDATE field_data_body SET body_summary = REPLACE(body_summary,'¿', '¿');
UPDATE field_data_body SET body_summary = REPLACE(body_summary,'á', 'á');
UPDATE field_data_body SET body_summary = REPLACE(body_summary,'ó', 'ó');
UPDATE field_data_body SET body_summary = REPLACE(body_summary,'ñ', 'ñ');
UPDATE field_data_body SET body_summary = REPLACE(body_summary,'ň', 'ñ');
UPDATE field_data_body SET body_summary = REPLACE(body_summary,'é', 'é');
UPDATE field_data_body SET body_summary = REPLACE(body_summary,'ú', 'ú');
UPDATE field_data_body SET body_summary = REPLACE(body_summary,'Ã', 'í');
UPDATE field_data_body SET body_summary = REPLACE(body_summary,'Â', ' ');
UPDATE field_data_body SET body_summary = REPLACE(body_summary,'¯', 'ï');

UPDATE field_revision_body SET body_value = REPLACE(body_value,'’', '’');
UPDATE field_revision_body SET body_value = REPLACE(body_value,'–', '–');
UPDATE field_revision_body SET body_value = REPLACE(body_value,'—', '—');
UPDATE field_revision_body SET body_value = REPLACE(body_value,'“', '“');
UPDATE field_revision_body SET body_value = REPLACE(body_value,'â€', '”');
UPDATE field_revision_body SET body_value = REPLACE(body_value,'¿', '¿');
UPDATE field_revision_body SET body_value = REPLACE(body_value,'á', 'á');
UPDATE field_revision_body SET body_value = REPLACE(body_value,'ó', 'ó');
UPDATE field_revision_body SET body_value = REPLACE(body_value,'ñ', 'ñ');
UPDATE field_revision_body SET body_value = REPLACE(body_value,'ň', 'ñ');
UPDATE field_revision_body SET body_value = REPLACE(body_value,'é', 'é');
UPDATE field_revision_body SET body_value = REPLACE(body_value,'ú', 'ú');
UPDATE field_revision_body SET body_value = REPLACE(body_value,'Ã', 'í');
UPDATE field_revision_body SET body_value = REPLACE(body_value,'Â', ' ');
UPDATE field_revision_body SET body_value = REPLACE(body_value,'¯', 'ï');

UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'’', '’');
UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'–', '–');
UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'—', '—');
UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'“', '“');
UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'â€', '”');
UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'¿', '¿');
UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'á', 'á');
UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'ó', 'ó');
UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'ñ', 'ñ');
UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'ň', 'ñ');
UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'é', 'é');
UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'ú', 'ú');
UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'Ã', 'í');
UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'Â', ' ');
UPDATE field_revision_body SET body_summary = REPLACE(body_summary,'¯', 'ï');

UPDATE menu_links SET link_title = REPLACE(link_title,'’', '’') WHERE menu_name like '%main%';
UPDATE menu_links SET link_title = REPLACE(link_title,'–', '–') WHERE menu_name like '%main%';
UPDATE menu_links SET link_title = REPLACE(link_title,'—', '—') WHERE menu_name like '%main%';
UPDATE menu_links SET link_title = REPLACE(link_title,'“', '“') WHERE menu_name like '%main%';
UPDATE menu_links SET link_title = REPLACE(link_title,'â€', '”') WHERE menu_name like '%main%';
UPDATE menu_links SET link_title = REPLACE(link_title,'¿', '¿') WHERE menu_name like '%main%';
UPDATE menu_links SET link_title = REPLACE(link_title,'á', 'á') WHERE menu_name like '%main%';
UPDATE menu_links SET link_title = REPLACE(link_title,'ó', 'ó') WHERE menu_name like '%main%';
UPDATE menu_links SET link_title = REPLACE(link_title,'ñ', 'ñ') WHERE menu_name like '%main%';
UPDATE menu_links SET link_title = REPLACE(link_title,'ň', 'ñ') WHERE menu_name like '%main%';
UPDATE menu_links SET link_title = REPLACE(link_title,'é', 'é') WHERE menu_name like '%main%';
UPDATE menu_links SET link_title = REPLACE(link_title,'ú', 'ú') WHERE menu_name like '%main%';
UPDATE menu_links SET link_title = REPLACE(link_title,'Ã', 'í') WHERE menu_name like '%main%';
UPDATE menu_links SET link_title = REPLACE(link_title,'Â', ' ') WHERE menu_name like '%main%';
UPDATE menu_links SET link_title = REPLACE(link_title,'¯', 'ï') WHERE menu_name like '%main%';
webengr’s picture

this seems like it could be very helpful, but I seem to be having issues cut/paste or download trying to get the characters to be replaced on my sql command line.   With putty I tried translations set to utf8 and to Win1250 also to no avail. hmmm.

’
webengr’s picture

this seems like it could be very helpful, but I seem to be having issues cut/paste or download trying to get the characters to be replaced on my sql command line.   With putty I tried translations set to utf8 and to Win1250 also to no avail. hmmm.

’