What?
A quick article showing my MySQL statement when I want to remove all the accents from foreign characters and return the English equivalent.

Why?
A content management system (CMS) that I'm working on has just gone international and started including the names of places in other countries. This is nice but its search engine doesn't work properly as it thinks "riviere" is different to "rivière". We need to ensure that a search for any of these kind of words will return results of similarly typed/sounding words.

How?
Here's just a splurge of SQL but I use this often enough:

UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'À', 'A');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Á', 'A');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Â', 'A');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ä', 'A');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Æ', 'AE');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ã', 'A');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Å', 'A');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ā', 'A');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'à', 'a');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'á', 'a');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'â', 'a');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ä', 'a');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'æ', 'ae');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ã', 'a');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'å', 'a');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ā', 'a');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ă', 'a');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Đ', 'D');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'È', 'E');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'É', 'E');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ê', 'E');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ë', 'E');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ē', 'E');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ė', 'E');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ę', 'E');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'è', 'e');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'é', 'e');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ê', 'e');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ë', 'e');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ē', 'e');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ė', 'e');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ę', 'e');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ế', 'e');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Î', 'I');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ï', 'I');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Í', 'I');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ī', 'I');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Į', 'I');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ì', 'I');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'î', 'i');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ï', 'i');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'í', 'i');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ī', 'i');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'į', 'i');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ì', 'i');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ĩ', 'i');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ô', 'O');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ö', 'O');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ò', 'O');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ó', 'O');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Œ', 'OE');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ø', 'O');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ō', 'O');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Õ', 'O');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ô', 'o');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ö', 'o');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ò', 'o');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ó', 'o');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'œ', 'oe');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ø', 'o');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ō', 'o');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'õ', 'o');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ộ', 'o');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Û', 'U');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ü', 'U');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ù', 'U');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ú', 'U');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ū', 'U');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'û', 'u');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ü', 'u');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ù', 'u');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ú', 'u');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ū', 'u');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ß', 'ss');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ç', 'C');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ć', 'C');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Č', 'C');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ç', 'c');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ć', 'c');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'č', 'c');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ł', 'L');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ł', 'l');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ñ', 'N');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ń', 'N');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ñ', 'n');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ń', 'n');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ś', 'S');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Š', 'S');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ś', 's');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'š', 's');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ș', 's');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ş', 's');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ț', 't');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ÿ', 'Y');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ý', 'y');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ÿ', 'y');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ž', 'Z');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ź', 'Z');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ż', 'Z');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ž', 'z');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ź', 'z');
UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ż', 'z');

Retrieve records with foreign characters:
SELECT * 
FROM  `myTable` 
WHERE  `myColumn` REGEXP  '[^A-Za-z0-9 -()\-.]'

Joomla! Additional
My Joomla website needed the following code to display the above foreign characters as entered into the Joomla administrators console with accents and all:
html_entity_decode(htmlentities($ftext, ENT_COMPAT,'UTF-8', true), ENT_COMPAT, 'UTF-8');

Add comment


Send