sql - Remove Accents DB2

Question

Does someone knows how to get rid of accents words in DB2 ?

I got that one bellow :

select UPPER(  'test'  || 'DescricaoDomino' || NVL('SiglaDomino', 'X')) from tbProcTeste  ;

And i got that result :

 1
 -------------------------------

     TESTDESCRICAODOMINOSIGLADOMINO

However i could receive a string with accents like this one "!":

      select UPPER(  'test!'  || 'DescricaoDomino' || NVL('SiglaDomino', 'X')) 
from  tbProcTeste  ;

And id like to get a same result above . Someone knows how do i do that ?

Result that i expected

1

---

 TESTDESCRICAODOMINOSIGLADOMINO

I dont have huge DB2 background . DB2 Version 11,1

Sincerly

Answer 1

Based on the mapping in this post https://stackoverflow.com/a/9667817/9525344 the following Db2 function will replace most(?) of the possible Unicode characters with diacritic marks with their simple Latin equivalent (which may, or may not be what is actually used as a replacement in a given language. E..g in German, ü is usually replaced with ue, not u)

CREATE OR REPLACE FUNCTION DB_STRIP_DIACRITICS(string VARCHAR(32000))
RETURNS VARCHAR(32000)
LANGUAGE SQL CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION
RETURN
REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(
REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(
REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(
REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(
REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(
REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(
REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(
REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(
REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(
 string,
'[á??????ǎ????????????à??ā????????]',                  'A'), 
'[?]',                                                 'AA'), 
'[????]',                                              'AE'), 
'[?]',                                                 'AO'), 
'[?]',                                                 'AU'), 
'[??]',                                                'AV'), 
'[?]',                                                 'AY'), 
'[????????]',                                          'B'), 
'[??????????]',                                        'C'), 
'[?????????????]',                                     'D'), 
'[??]',                                                'DZ'), 
'[é?ě??ê??????????è??ē??????????]',                    'E'), 
'[?]',                                                 'ET'), 
'[????]',                                              'F'), 
'[????????????]',                                      'G'), 
'[??????????]',                                        'H'), 
'[í?ǐ??????ì??ī?????]',                                'I'), 
'[?]',                                                 'IJ'), 
'[?]',                                                 'IS'), 
'[???]',                                               'J'), 
'[???????????]',                                       'K'), 
'[?????????????????]',                                 'L'), 
'[?]',                                                 'LJ'), 
'[??????]',                                            'M'), 
'[??????????????]',                                    'N'), 
'[?]',                                                 'NJ'), 
'[ó?ǒ?????????????ò??????????ō??????????????]',        'O'), 
'[??]',                                                'OE'), 
'[?]',                                                 'OI'), 
'[?]',                                                 'OO'), 
'[??]',                                                'OU'), 
'[????????]',                                          'P'), 
'[??]',                                                'Q'), 
'[????????????????]',                                  'R'), 
'[????????????]',                                      'S'), 
'[?????????????]',                                     'T'), 
'[?]',                                                 'TZ'), 
'[ú?ǔ??üǘǚǜǖ????ù????????ū???????]',                   'U'), 
'[??????]',                                            'V'), 
'[?]',                                                 'VY'), 
'[????????]',                                          'W'), 
'[??]',                                                'X'), 
'[Y????????????]',                                     'Y'), 
'[??????????]',                                        'Z'), 
'[á??????ǎa???????????à??ā??????????]',                'a'), 
'[?]',                                                 'aa'), 
'[????]',                                              'ae'), 
'[?]',                                                 'ao'), 
'[?]',                                                 'au'), 
'[??]',                                                'av'), 
'[?]',                                                 'ay'), 
'[????????]',                                          'b'), 
'[???????????]',                                       'c'), 
'[???????????????]',                                   'd'), 
'[??]',                                                'dz'), 
'[é?ě??ê??????????è??ē?????????????]',                 'e'), 
'[?]',                                                 'et'), 
'[?????]',                                             'f'), 
'[?]',                                                 'ff'), 
'[?]',                                                 'ffi'), 
'[?]',                                                 'ffl'), 
'[?]',                                                 'fi'), 
'[?]',                                                 'fl'), 
'[???????????ɡ?]',                                     'g'), 
'[??????????????]',                                    'h'), 
'[?]',                                                 'hv'), 
'[?í?ǐ?????ì??ī???????]',                              'i'), 
'[?]',                                                 'ij'), 
'[?]',                                                 'is'), 
'[????????]',                                          'j'), 
'[????????????]',                                      'k'), 
'[??????????????????]',                                'l'), 
'[?]',                                                 'lj'), 
'[????????]',                                          'm'), 
'[ńň?????????????]',                                   'n'), 
'[?]',                                                 'nj'), 
'[?ó?ǒ?????????????ò???????????ō???????????????]',     'o'), 
'[??]',                                                'oe'), 
'[?]',                                                 'oi'), 
'[?]',                                                 'oo'), 
'[?]',                                                 'ou'), 
'[?????????]',                                         'p'), 
'[????]',                                              'q'), 
'[???????????????????????]',                           'r'), 
'[???????????????????]',                               's'), 
'[?]',                                                 'st'), 
'[?????????????????]',                                 't'), 
'[?]',                                                 'th'), 
'[?]',                                                 'tz'), 
'[?ú?ǔ??üǘǚǜǖ????ù????????ū????????]',                 'u'), 
'[?]',                                                 'ue'), 
'[?]',                                                 'um'), 
'[?????????]',                                         'v'), 
'[?]',                                                 'vy'), 
'[?????????]',                                         'w'), 
'[????]',                                              'x'), 
'[?y????????????]',                                    'y'), 
'[??????????????]',                                    'z')