Search code examples
mysqlutf-8replacenon-ascii-characters

Mysql replace all special unicode characters with their ascii counterpart

I have a field with encoding utf8-general-ci in which many values contain non-ascii characters. I want to

  1. Search for all fields with any non-ascii characters

  2. Replace all non-ascii characters with their corresponding ascii version.

For example: côte-d'ivoire should be replaced with cote-d-i'voire, são-tomé should be replaced with sao-tome, etc.

How do I achieve this? If I just change the field type to ascii, non-ascii characters get replaced by '?'. I am not even able to search for all such fields using

RLIKE '%[^a-z]%'

For example

SELECT columname 
FROM tablename 
WHERE NOT columname REGEXP '[a-z]';

returns an empty set.

Thanks

Solution

  • An sql fiddle example is at

    http://www.sqlfiddle.com/#!2/c1d90/1/0

    the query to select is 

    select * from test where maintext rlike  '[^\x00-\x7F]'

    Hope this helps