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

Question

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?

score 2 · Accepted Answer · answered Nov 23 '12 at 04:24

2

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

answered Nov 23 '12 at 04:24

Jeffrey Kemp

59,135
14
106
158

score 0 · Answer 2 · answered Nov 22 '12 at 18:14

0

The function below works fine in Oracle for almost all characters in Portuguese language, but it doesn't work for the character ‘ã’ or ‘Ã’

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

answered Nov 22 '12 at 18:14

Rafael Borja

4,487
7
29
33

Sid Sousa · Answer 3 · 2016-09-19T13:39:28.980

0

Try to use

SELECT CONVERT('Ä Ê Í Ó Ø A B C D E ã Ã', 'us7ascii', 'ee8mswin1250') FROM DUAL

This will remove characters ‘ã’ or ‘Ã’.

edited Sep 19 '16 at 13:39

answered Aug 25 '16 at 16:47

Sid Sousa

31
3

This is exactly the same as the other answers – Aug 25 '16 at 17:21
@AlekSandar, No, it's not the same. Check the encoding and you'll see. – Sid Sousa Sep 12 '16 at 18:00

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

3 Answers3