MySQL: Find and replace non-ASCII characters (ie, after an Excel import)

gistfile1.sql

-- This finds all records with non-ASCII characters
SELECT * FROM locations WHERE NOT HEX(name) REGEXP '^([0-7][0-9A-F])*$';

UPDATE locations SET name = REPLACE(name, '‡', 'á'); -- HEX E280A1 / HEX C3A1
UPDATE locations SET name = REPLACE(name, 'Ž', 'é'); -- HEX C5BD / HEX C3A9
UPDATE locations SET name = REPLACE(name, 'Ÿ', 'ü'); -- HEXC5B8 / HEX C3BC
UPDATE locations SET name = REPLACE(name, 'š', 'ö'); -- HEX C5A1 / HEX C3B6
UPDATE locations SET name = REPLACE(name, 'Š', 'ä'); -- HEX C5A0 / HEX C3A4
UPDATE locations SET name = REPLACE(name, '§', 'ß'); -- HEX C2A7 / HEX C39F
UPDATE locations SET name = REPLACE(name, 'Õ', '\''); -- HEX C395 / HEX 27
UPDATE locations SET name = REPLACE(name, 'ž', 'û'); -- HEX C5BE / HEX C3BB
UPDATE locations SET name = REPLACE(name, 'ƒ', 'É'); -- HEX C692 / HEX C389
UPDATE locations SET name = REPLACE(name, '‰', 'â'); -- HEX E280B0 / HEX C3A2
UPDATE locations SET name = REPLACE(name, '™', 'ô'); -- HEX E284A2 / HEX C3B4
UPDATE locations SET name = REPLACE(name, '‹', 'ã'); -- HEX E280B9 / HEX C3A3
UPDATE locations SET name = REPLACE(name, '…', 'Ö'); -- HEX E280A6 / HEX C396
UPDATE locations SET name = REPLACE(name, '‘', 'ë'); -- HEX E28098 / HEX C3AB
UPDATE locations SET name = REPLACE(name, '›', 'õ'); -- HEX E280BA / HEX C3B5
UPDATE locations SET name = REPLACE(name, '†', 'Ü'); -- HEX E280A0 / HEX C39C
UPDATE locations SET name = REPLACE(name, '«', '\''); -- HEX C2AB / HEX 27
UPDATE locations SET name = REPLACE(name, '’', 'í'); -- HEX E28099 / HEX C3AD
UPDATE locations SET name = REPLACE(name, '—', 'ó'); -- HEX E28094 / HEX C3B3
UPDATE locations SET name = REPLACE(name, '–', 'ñ'); -- HEX E28093 / HEX C3B1
UPDATE locations SET name = REPLACE(name, 'œ', 'ú'); -- HEX C593 / HEX C3BA
UPDATE locations SET name = REPLACE(name, '„', 'Ñ'); -- HEX E2809E / HEX C391
UPDATE locations SET name = REPLACE(name, '”', 'î'); -- HEX E2809D / HEX C3AE
UPDATE locations SET name = REPLACE(name, 'ç', 'Á'); -- HEX C3A7 / HEX C381
UPDATE locations SET name = REPLACE(name, 'ê', 'Í'); -- HEX C3AA / HEX C38D
UPDATE locations SET name = REPLACE(name, '•', 'ï'); -- HEX E280A2 / HEX C3AF
UPDATE locations SET name = REPLACE(name, 'ˆ', 'à'); -- HEX CB86 / HEX C3A0
UPDATE locations SET name = REPLACE(name, 'ò', 'Ú'); -- HEX C3B2 / HEX C39A

-- Be careful with these ones; the search character was blank when copied in
UPDATE locations SET name = REPLACE(name, '', 'ç'); -- HEX C28D / HEX C3A7
UPDATE locations SET name = REPLACE(name, '', 'è'); -- HEX C28F / HEX C3A8
UPDATE locations SET name = REPLACE(name, '', 'ê'); -- HEX C290 / HEX C3AA

You can't imagine how much this helps right now! 🥇