Search code examples
oracle-databasecharacter-encodingplsqlasciinon-ascii-characters

Diatric string conversion in Oracle – TO_ASCII alternative in Oracle - How to remove accents and special characters

I need to convert strings with diatrics to as ASCII version of it.

As an example the string “Caicó” is converted to “Caico” and “À bientôt” is converted to “A bientot”. It’s a usual problem with lots of European languages that uses diactrics to decorate a regular Latin letter to change its pronunciation.

The problem is when a database is inputted with ASCII (regular) encoding and you need to compare it with Unicode characters.

The function TO_ASCII works perfectly fine in PostegreSQL for the Portuguese language. Any idea of a similar function on Oracle or a better approach to compare such strings in Oracle?

Solution

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

    SELECT CONVERT(TRANSLATE('Ä Ê Í Ó Ø A B C D E ã Ã ã Ã'
   ,'ãÃ','aA')
   ,'US7ASCII','WE8ISO8859P1') from dual