how replace accented letter in a varchar2 column in oracle

Question

I have a varchar2 column named NAME_USER. for example the data is: JUAN ROMÄN but I try to show JUAN ROMAN, replace Á to A in my statement results. How Can I do that?. Thanks in advance.

Answer 1

Use convert function with the appropriate charset

select CONVERT('JUAN ROMÄN', 'US7ASCII') from dual;

below are the charset which can be used in oracle:

US7ASCII: US 7-bit ASCII character set
WE8DEC: West European 8-bit character set
WE8HP: HP West European Laserjet 8-bit character set
F7DEC: DEC French 7-bit character set
WE8EBCDIC500: IBM West European EBCDIC Code Page 500
WE8PC850: IBM PC Code Page 850
WE8ISO8859P1: ISO 8859-1 West European 8-bit character set

Answer 2

You could use replace, regexp_replace or translate, but they would each require you to map all possible accented characters to their unaccented versions.

Alternatively, there's a function called nlssort() which is typically used to override the default language settings used for the order by clause. It has an option for accent-insensitive sorting, which can be creatively misused to solve your problem. nlssort() returns a binary, so you have to convert back to varchar2 using utl_raw.cast_to_varchar2():

select utl_raw.cast_to_varchar2(nlssort(NAME_USER, 'nls_sort=binary_ai'))
from YOUR_TABLE;

Try this, for a list of accented characters from the extended ASCII set, together with their derived, unaccented values:

select level+192 ascii_code,
       chr(level+192) accented,
       utl_raw.cast_to_varchar2(nlssort(chr(level+192),'nls_sort=binary_ai')) unaccented
from dual
connect by level <= 63
order by 1;

Not really my answer - I've used this before and it seemed to work ok, but have to credit this post: https://community.oracle.com/thread/1117030

ETA: nlssort() can't do accent-insensitive without also doing case-insensitive, so this solution will always convert to lower case. Enclosing the expression above in upper() will of course get your example value back to "JUAN ROMAN". If your values can be mixed case, and you need to preserve the case of each character, and initcap() isn't flexible enough, then you'll need to write a bit of PL/SQL.