0

How could I replace accents with normal characters in DB2?

For example: ëèé becomes eee

edit: I could use the REPLACE function in my function but I don't want to catch ALL the possibilites because it seems endless to me.

GregD
  • 1,884
  • 2
  • 28
  • 55
  • 2
    :-/ An umlaut is a normal character ... unless you live in America :-) – Aaron Digulla Jul 17 '14 at 10:00
  • 1
    Did you try `CAST(c_uni AS VARCHAR(10) CCSID ASCII)`? – Aaron Digulla Jul 17 '14 at 10:02
  • I've tried: `select CAST(d.someValue AS VARCHAR(10) CCSID ASCII) FROM DOSSIER d` and I've got: `The clause "CCSID ASCII" is invalid for this database. SQL Code: -622, SQL State: 56031` – GregD Jul 17 '14 at 12:31
  • Please define 'normal' characters. Probably, you are meaning the ASCII table, but all characters are normal, because they are defined in UNICODE. That is the lack of knowledge about other languages and computing systems. – AngocA Jul 17 '14 at 14:20
  • Answers to this question are likely platform dependent. Remember IBM i and z/OS are EBCDIC based systems, while LUW systems are ASCII (or related set). The three platforms do have many basics in common, but significant technical differences such as various built in functions. Please specify which DB2 platform you are asking about (or answering for). – WarrenT Jul 17 '14 at 16:43
  • IBM i (formerly known as iSeries or AS/400) or IBM z/OS (S/390 lineage) or AIX, Linix, or Windows on an IBM server? – WarrenT Jul 18 '14 at 15:54
  • There is an example here. https://stackoverflow.com/questions/48100215/remove-accents-db2/52304253#52304253 Works in Db2 for LUW, not tested on the other platforms – Paul Vernon Sep 12 '18 at 22:45

2 Answers2

1

One idea is to write a java function like: http://www.drillio.com/en/software-development/java/removing-accents-diacritics-in-any-language/

public static String removeAccents(String text) {
    return text == null ? null
        : Normalizer.normalize(text, Form.NFD)
            .replaceAll("\\p{InCombiningDiacriticalMarks}+", "");
}

Now you can register that function in DB2 and use it from sql. See sqlj.install_jar in db2 documentation

Lennart - Slava Ukraini
  • 6,936
  • 1
  • 20
  • 32
0

There is not an utility for this replacement, because this is not something generic, but a very specific case.

For example, in French we have: çàéèùêô€œ

In Spanish we have: áéíóúñüÑ

German and Danish have different characters.

Even some English words have foreign chars: façade.

How are you going to translate them? Even if it seem endless you have to provide your own replacement. First, you need to create a matching list for the language you are going to translate, and then create that endless list.

BTW, why do you want to translate them? Any well application supports Unicode, only old or bad applications do not support Unicode correctly.

Finally, you can create a more flexible method to change characters if you use Regular Expression. And in DB2, you can use them with xQuery.

AngocA
  • 7,655
  • 6
  • 39
  • 55
  • 1
    I agree, however there are situations where you want to remove diacritics. Take for example a generated column that you can use for search, just remove diacritics from the search argument and query the generated column. – Lennart - Slava Ukraini Jul 17 '14 at 14:44
  • It's indeed a column used for searching. – GregD Jul 17 '14 at 14:58
  • Yes, you are right. What is true is that the conversion list should have done in all cases. – AngocA Jul 17 '14 at 15:04