Replacing charachers in a mysql database

Question

I have transfered a big phpbb3 database from a latin1 database to an utf-8 Everything went ok, now I need to change the "strange" characters in the database. Yesterday I found a method that worked, now I can't find it anymore, I tried:

update phpbb_topics set topic_title = replace(topic_title, 'Ã', 'à');
update phpbb_topics set topic_title = replace(topic_title, 'è', 'è');

Basically I need to change all the à characters in the table field to à and so on.

How can you do this?

Thanks

Answer 1

I would dump it to a .sql file and then replace the chars with sed or a text editor( if the file is not to large). After that you run the script again. Maybe there is a way to do this in the database directly but I don't know it.

The problem about my solution is that its not atomic. Changes to the db between the dump and the reinsertion are lost.

Greetings Raffael

Answer 2

update phpbb_topics set topic_title = replace(replace(topic_title, 'è', 'è'), 'Ã', 'à');

You should answer yourself following questions:

• what binary data is stored in database (use ascii() function or SELECT ... INTO DUMPFILE)
• what binary data is sent to client (it is not always the same as stored)
• how your client shows received binary data? (try different clients and different options)