Mysql Character Mapping

Question

Hi,

I would like to map foreign characters, especially Turkish characters, to their Latin-1 equivalent in Mysql.

For example,

Select name FROM users WHERE id = 1

Result = Çakır

but I would like to get it as:

Cakir

or

Özel -> Ozel

There are couple of Turkish characters and they all have Latin-1 equivalents. ( http://webdesign.about.com/od/localization/l/blhtmlcodes-tr.htm )

How can I do that in Mysql by using SQL query? I am also ok with manual mapping since this query will be run couple of times in a day, so the performance impact is not important at this moment.

Thanks in advance,

Answer 1

How can I do that in Mysql by using SQL query?

If you only need to target those few characters commonly used in Turkish, then you could just use a (hideous) string replace:

SELECT
    REPLACE(REPLACE(
        REPLACE(REPLACE(
            REPLACE(REPLACE(
                REPLACE(REPLACE(
                    REPLACE(REPLACE(
                        REPLACE(REPLACE(
                            name,
                        'İ', 'I'), 'ı' 'i'),
                    'Ö', 'O'), 'ö', 'o'),
                'Ü', 'U'), 'ü', 'u'),
            'Ç', 'C'), 'ç', 'c'),
        'Ğ', 'G'), 'ğ', 'g'),
    'Ş', 'S'), 'ş', 's')
    AS name
FROM users
WHERE id=1

You could put this in a stored procedure if you want, but really you're probably better off doing this in a proper programming (scripting) language outside the database. You'd get better string replace/translation tools and more general solutions than this.

For example removing diacriticals is fairly easy using Unicode normalisation. Transliteration in general is a tricky and potentially language-specific task which can be too hard for the database layer.