Welcome to Joel Lipman .Com

Preparing Content...


Loading...

Our Website Development Notes

We hope this helps!

Articles // SQL // MySQL

Convert foreign characters to English alphabet

Monday, 11th July 2016
2,607 Reads
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:
  1. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'À', 'A');  
  2. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Á', 'A');  
  3. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Â', 'A');  
  4. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ä', 'A');  
  5. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Æ', 'AE');  
  6. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ã', 'A');  
  7. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Å', 'A');  
  8. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ā', 'A');  
  9. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'à', 'a');  
  10. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'á', 'a');  
  11. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'â', 'a');  
  12. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ä', 'a');  
  13. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'æ', 'ae');  
  14. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ã', 'a');  
  15. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'å', 'a');  
  16. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ā', 'a');  
  17. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ă', 'a');  
  18. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Đ', 'D');  
  19. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'È', 'E');  
  20. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'É', 'E');  
  21. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ê', 'E');  
  22. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ë', 'E');  
  23. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ē', 'E');  
  24. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ė', 'E');  
  25. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ę', 'E');  
  26. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'è', 'e');  
  27. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'é', 'e');  
  28. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ê', 'e');  
  29. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ë', 'e');  
  30. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ē', 'e');  
  31. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ė', 'e');  
  32. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ę', 'e');  
  33. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ế', 'e');  
  34. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Î', 'I');  
  35. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ï', 'I');  
  36. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Í', 'I');  
  37. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ī', 'I');  
  38. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Į', 'I');  
  39. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ì', 'I');  
  40. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'î', 'i');  
  41. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ï', 'i');  
  42. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'í', 'i');  
  43. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ī', 'i');  
  44. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'į', 'i');  
  45. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ì', 'i');  
  46. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ĩ', 'i');  
  47. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ô', 'O');  
  48. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ö', 'O');  
  49. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ò', 'O');  
  50. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ó', 'O');  
  51. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Œ', 'OE');  
  52. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ø', 'O');  
  53. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ō', 'O');  
  54. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Õ', 'O');  
  55. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ô', 'o');  
  56. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ö', 'o');  
  57. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ò', 'o');  
  58. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ó', 'o');  
  59. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'œ', 'oe');  
  60. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ø', 'o');  
  61. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ō', 'o');  
  62. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'õ', 'o');  
  63. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ộ', 'o');  
  64. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Û', 'U');  
  65. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ü', 'U');  
  66. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ù', 'U');  
  67. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ú', 'U');  
  68. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ū', 'U');  
  69. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'û', 'u');  
  70. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ü', 'u');  
  71. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ù', 'u');  
  72. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ú', 'u');  
  73. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ū', 'u');  
  74. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ß', 'ss');  
  75. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ç', 'C');  
  76. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ć', 'C');  
  77. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Č', 'C');  
  78. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ç', 'c');  
  79. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ć', 'c');  
  80. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'č', 'c');  
  81. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ł', 'L');  
  82. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ł', 'l');  
  83. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ñ', 'N');  
  84. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ń', 'N');  
  85. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ñ', 'n');  
  86. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ń', 'n');  
  87. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ś', 'S');  
  88. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Š', 'S');  
  89. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ś', 's');  
  90. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'š', 's');  
  91. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ș', 's');  
  92. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ş', 's');  
  93. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ț', 't');  
  94. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ÿ', 'Y');  
  95. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ý', 'y');  
  96. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ÿ', 'y');  
  97. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ž', 'Z');  
  98. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ź', 'Z');  
  99. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'Ż', 'Z');  
  100. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ž', 'z');  
  101. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ź', 'z');  
  102. UPDATE `myTable` t SET t.`myColumn`=REPLACE(t.`myColumn`, 'ż', 'z');

Retrieve records with foreign characters:
  1. SELECT *  
  2. FROM `myTable`  
  3. 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:
  1. html_entity_decode(htmlentities($ftext, ENT_COMPAT,'UTF-8', true), ENT_COMPAT, 'UTF-8');



Recent Comments

Gravatar for Art
Oracle: order by subquery missing right parenthesis
Hello, what if I would like to add rownum to that code, but in my case it's all is subquery? How can I do this ?

20 Jan


Gravatar for Robert
CharIndex Reverse - find occurrence starting from end of string in TSQL
Very helpful thanks! This worked for me as well, and is a little shorter. Not sure if there are drawbacks. SELECT RIGHT(@Haystack,CHARINDEX(@Delimiter,REVERSE(@Haystack))-LEN(@Delimiter))

29 Dec


Gravatar for Translation

27 Dec


Gravatar for Tibbe
JComments 2.3.0 with ReCaptcha in Joomla 2.5.x
Hi there, This looks like a great solution to get rid of spam comments. How to integrate this in Joomla 3.x? Step 2 I did in settings.xml, but no result in frontend. Kind Regards, Tibbe

16 Dec


Gravatar for sach|n
SSRS Repeat Headers in PDF Report
Yes its working for me..thnx Joel.

7 Oct