nix, shell, perl, php, mysql and mac os x tips and tricks

Monday, December 18, 2017

MySQL search and replace unicode (hexidecimal) characters with HTML entities

Not really pretty, but this will replace Latin-1 Supplement and Latin Extended-A characters (plus permutations of quotes, dashes and apostrophes) with their corresponding HTML entities. NOTE: if you use this, copy the queries from the source code of this page, the browser is translating the entities. Query is operating on a column called DESCRIPTION.
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A0'),' ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A1'),'¡');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A2'),'¢');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A3'),'£');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A4'),'¤');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A5'),'¥');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A6'),'¦');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A7'),'§');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A8'),'¨');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A9'),'©');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00AA'),'ª');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00AB'),'«');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00AC'),'¬');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00AD'),'­');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00AE'),'®');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00AF'),'¯');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B0'),'°');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B1'),'±');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B2'),'²');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B3'),'³');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B4'),'´');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B5'),'µ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B6'),'¶');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B7'),'·');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B8'),'¸');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B9'),'¹');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00BA'),'º');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00BB'),'»');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00BC'),'¼');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00BD'),'½');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00BE'),'¾');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00BF'),'¿');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C0'),'À');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C1'),'Á');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C2'),'Â');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C3'),'Ã');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C4'),'Ä');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C5'),'Å');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C6'),'Æ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C7'),'Ç');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C8'),'È');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C9'),'É');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00CA'),'Ê');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00CB'),'Ë');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00CC'),'Ì');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00CD'),'Í');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00CE'),'Î');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00CF'),'Ï');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D0'),'Ð');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D1'),'Ñ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D2'),'Ò');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D3'),'Ó');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D4'),'Ô');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D5'),'Õ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D6'),'Ö');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D9'),'Ù');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00DA'),'Ú');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00DB'),'Û');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00DC'),'Ü');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00DD'),'Ý');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00DE'),'Þ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00DF'),'ß');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E0'),'à');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E1'),'á');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E2'),'â');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E3'),'ã');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E4'),'ä');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E5'),'å');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E6'),'æ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E7'),'ç');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E8'),'è');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E9'),'é');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00EA'),'ê');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00EB'),'ë');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00EC'),'ì');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00ED'),'í');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00EE'),'î');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00EF'),'ï');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F0'),'ð');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F1'),'ñ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F2'),'ò');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F3'),'ó');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F4'),'ô');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F5'),'õ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F6'),'ö');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F7'),'÷');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F8'),'ø');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F9'),'ù');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00FA'),'ú');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00FB'),'û');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00FC'),'ü');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00FD'),'ý');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00FE'),'þ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0100'),'Ā');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0101'),'ā');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0102'),'Ă');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0103'),'ă');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0104'),'Ą');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0105'),'ą');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0106'),'Ć');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0107'),'ć');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0108'),'Ĉ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0109'),'ĉ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'010A'),'Ċ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'010B'),'ċ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'010C'),'Č');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'010D'),'č');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'010E'),'Ď');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'010F'),'ď');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0110'),'Đ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0111'),'đ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0112'),'Ē');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0113'),'ē');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0114'),'&Ebreve;');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0115'),'&ebreve;');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0116'),'Ė');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0117'),'ė');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0118'),'Ę');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0119'),'ę');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'011A'),'Ě');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'011B'),'ě');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'011C'),'Ĝ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'011D'),'ĝ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'011E'),'Ğ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'011F'),'ğ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0120'),'Ġ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0121'),'ġ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0122'),'Ģ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0124'),'Ĥ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0125'),'ĥ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0126'),'Ħ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0127'),'ħ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0128'),'Ĩ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0129'),'ĩ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'012A'),'Ī');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'012B'),'ī');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'012C'),'&Ibreve;');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'012D'),'&ibreve;');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'012E'),'Į');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'012F'),'į');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0130'),'İ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0131'),'ı');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0132'),'IJ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0133'),'ij');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0134'),'Ĵ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0135'),'ĵ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0136'),'Ķ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0137'),'ķ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0139'),'Ĺ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'013A'),'ĺ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'013B'),'Ļ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'013C'),'ļ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'013D'),'Ľ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'013E'),'ľ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0141'),'Ł');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0142'),'ł');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0143'),'Ń');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0144'),'ń');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0145'),'Ņ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0146'),'ņ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0147'),'Ň');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0148'),'ň');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'014A'),'Ŋ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'014B'),'ŋ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'014C'),'Ō');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'014D'),'ō');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'014E'),'&Obreve;');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'014F'),'&obreve;');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0150'),'Ő');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0151'),'ő');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0152'),'Œ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0153'),'œ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0154'),'Ŕ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0155'),'ŕ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0156'),'Ŗ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0157'),'ŗ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0158'),'Ř');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0159'),'ř');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'015A'),'Ś');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'015B'),'ś');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'015C'),'Ŝ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'015D'),'ŝ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'015E'),'Ş');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'015F'),'ş');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0160'),'Š');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0161'),'š');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0162'),'Ţ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0163'),'ţ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0164'),'Ť');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0165'),'ť');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0166'),'Ŧ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0167'),'ŧ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0168'),'Ũ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0169'),'ũ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'016A'),'Ū');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'016B'),'ū');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'016C'),'Ŭ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'016D'),'ŭ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'016E'),'Ů');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'016F'),'ů');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0170'),'Ű');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0171'),'ű');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0172'),'Ų');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0173'),'ų');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0174'),'Ŵ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0175'),'ŵ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0176'),'Ŷ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0177'),'ŷ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0178'),'Ÿ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0179'),'Ź');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'017A'),'ź');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'017B'),'Ż');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'017C'),'ż');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'017D'),'Ž');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'017E'),'ž');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D7'),'×');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0096'),'–');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0085'),'…');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'009A'),'š');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'008A'),'Š');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'2013'),'—');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'2014'),'–');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'2015'),'&mdash');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E2'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C3'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C2'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0080'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0082'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0083'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0000'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'009C'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'009D'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0093'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0094'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E2'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C3'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C2'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0080'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0082'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0083'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0000'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0099'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00BA'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B9'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0098'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'2018'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'201A'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'2019'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'2039'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'203A'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0092'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, '—','—');

Monday, December 11, 2017

Find all non-ascii characters in MySQl column

SELECT *
  FROM products 
 WHERE DESCRIPTION <> CONVERT(DESCRIPTION USING ASCII)