Detecting utf8 broken characters in MySQL

Question

I've got a database with a bunch of broken utf8 characters scattered across several tables. The list of characters isn't very extensive AFAIK (áéíúóÁÉÍÓÚÑñ)

Fixing a given table is very straightforward

update orderItem set itemName=replace(itemName,'á','á'); 

But I can't get a way of detecting the broken characters. If I do something like

SELECT * FROM TABLE WHERE field LIKE "%Ã%"; 

I get nearly all the fields because of the collation (Ã=a). All broken characters so far start with an "Ã". The database is in spanish so this particular character isn't used

The list of broken chars I've got so far is

á = á é = é í- = í ó = ó ñ = ñ á = Á 

Any idea of how to make this SELECT to work as intended? (a binary search or something like that)

Answer 1

I fixed with

UPDATE wp_zcs9ck_posts_copy SET post_title =      CONVERT(BINARY CONVERT(post_title USING latin1) USING utf8); 

Complete solution: http://jonisalonen.com/2012/fixing-doubly-utf-8-encoded-text-in-mysql/

Answer 2

UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'á','á'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'ä','ä'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'é','é'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'í©','é'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'ó','ó'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'íº','ú'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'ú','ú'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'ñ','ñ'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'í‘','Ñ'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'Ã','í'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'–','–'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'’','\''); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'…','...'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'–','-'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'“','"'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'â€','"'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'‘','\''); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'•','-'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'‡','c'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'Â','');