Converting diacritical strings in Oracle - an alternative to TO_ASCII in Oracle - how to remove accents and special characters

Question

I need to convert diacritical lines to ASCII version.

As an example, the string “Caicó” is converted to “Caico”, and “À bientôt” is converted to “A bientot”. This is a common problem with many European languages, in which diacritics adorn an ordinary Latin letter to change its pronunciation.

The problem is that the database is entered with ASCII (regular) encoding, and you need to compare it with Unicode characters.

The TO_ASCII function TO_ASCII fine in PostegreSQL for the Portuguese language. Any idea of ​​a similar function in Oracle, or a better approach for comparing such strings in Oracle?

Answer 1

Would something like this be suitable? maybe wrap it in a function?

 SELECT CONVERT(TRANSLATE('Ä Ê Í Ó Ø ABCDE ã à ã Ã' ,'ãÃ','aA') ,'US7ASCII','WE8ISO8859P1') from dual 

Answer 2

The function below works fine in Oracle for almost all characters in Portuguese, but it does not work for the character 'ã or' Ã

 SELECT CONVERT('Ä Ê Í Ó Ø ABCDE ã Ã','US7ASCII','WE8ISO8859P1') from dual 

Answer 3

Try using

 SELECT CONVERT('Ä Ê Í Ó Ø ABCDE ã Ã', 'us7ascii', 'ee8mswin1250') FROM DUAL 

This will delete the characters 'ã or' Ã.