Translate or convert French accented characters to base ASCII characters

Question

We have a Customer table with the "First name" and "Last name" columns. There are million of records in the table. Few of the records have French Accented charaters.

Example: Adélaïde, Amélie , etc.

I am looking for a DB2 function that can convert or translate accents into base ASCII characters.

Example:

Input: Amélie

Output: Amelia

I know we have a solution in Java. We can use Apache Commons StringUtils library to convert.

String input = StringUtils.stripAccents("Tĥïŝ ĩš â fůňķŷ Šťŕĭńġ");
System.out.println(input);

As of release 3.4, this does not work for Ø or Ł.

Below solution works and covers all the characters. translate(col,'aAeEiIoOuUaAeEuUaAeEiIoOuUeEiIoOuUcCnNaaaiAAAIoOyydDYooOOA','áÁéÉíÍóÓúÚàÀèÈùÙâÂêÊîÎôÔûÛëËïÏöÖüÜçÇñÑäãåìÄÃÅÌøØÿýðÐÝòõÒÕæ')

Note: æ is converted to A

Do you consider also, say, devanagari or arabic somehow _not normal_? — mustaccio, Sep 23 '16 at 00:06
For the record there is a Db2 LUW example here https://stackoverflow.com/questions/48100215/remove-accents-db2/52304253#52304253 — Paul Vernon, Sep 12 '18 at 22:47

score 1 · Accepted Answer · answered Sep 23 '16 at 06:34

1

you can use the translate function if you want :

  translate(upper(ColName),'AAAEEEIIIOOOUUU','ÁÀÄÉÈËÍÌÏÓÒÖÚÙÜ')

answered Sep 23 '16 at 06:34

Esperento57

16,521
3
39
45

using below command covers all the characters....translate(colname,'aAeEiIoOuUaAeEuUaAeEiIoOuUeEiIoOuUcCnNaaaiAAAIoOyydDYooOOA','áÁéÉíÍóÓúÚàÀèÈùÙâÂêÊîÎôÔûÛëËïÏöÖüÜçÇñÑäãåìÄÃÅÌøØÿýðÐÝòõÒÕæ') – Vinayak Dornala Oct 05 '16 at 19:14

score 0 · Answer 2 · answered Jun 24 '20 at 09:23

https://www.senat.fr/questions/base/2018/qSEQ180303860.html

states the only characters authorized for french citizens names are

à-â-ä-é-è-ê-ë-ï-î-ô-ö-ù-û-ü-ÿ-ç and æ and œ so the answer would be

replace(replace((translate(lower(ColName),'aaaeeeeiioouuuyc','àâäéèêëïîôöùûüÿç')),'æ','ae'),'œ','oe')

Translate or convert French accented characters to base ASCII characters

2 Answers2