Skip to content

Instantly share code, notes, and snippets.

@Shagshag
Created December 13, 2017 08:52
Show Gist options
  • Save Shagshag/018da62b616ed66362b13d9794070b91 to your computer and use it in GitHub Desktop.
Save Shagshag/018da62b616ed66362b13d9794070b91 to your computer and use it in GitHub Desktop.
Replace bad encoding rubbishes like é by UTF-8 characters
UPDATE table SET field = REPLACE(field, CAST(_latin1'à' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'à');
UPDATE table SET field = REPLACE(field, CAST(_latin1'é' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'é');
UPDATE table SET field = REPLACE(field, CAST(_latin1'è' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'è');
UPDATE table SET field = REPLACE(field, CAST(_latin1'ê' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'ê');
UPDATE table SET field = REPLACE(field, CAST(_latin1'ù' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'ù');
UPDATE table SET field = REPLACE(field, CAST(_latin1'ç' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'ç');
UPDATE table SET field = REPLACE(field, CAST(_latin1'ë' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'ë');
UPDATE table SET field = REPLACE(field, CAST(_latin1'ô' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'ô');
UPDATE table SET field = REPLACE(field, CAST(_latin1'â' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'â');
UPDATE table SET field = REPLACE(field, CAST(_latin1'û' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'û');
UPDATE table SET field = REPLACE(field, CAST(_latin1'î' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'î');
UPDATE table SET field = REPLACE(field, CAST(_latin1'ï' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'ï');
UPDATE table SET field = REPLACE(field, CAST(_latin1'Ç' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'Ç');
UPDATE table SET field = REPLACE(field, CAST(_latin1'É' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'É');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment