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.

Wealth

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

Kaffir

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.

Obliging

Good answer, you also need to check that uppercase characters are converted to lower case. In this case you should apply an UPPER, to keep it in initial format. – Reptile

@Obliging How do you remove null characters added at the end of string? – Porscheporsena

@ErickAstoOblitas That should really be a new question, if there isn't one already. If you mean ASCII null, then rtrim(your_string,chr(0)) should do it. – Obliging

Answer 3

You can use regular expressions:

SELECT regexp_replace('JUAN ROMÄNí','[[=A=]]+','A' )
FROM dual;

Wrapping

Answer 4

select replace('JUAN ROMÄN','Ä','A')
from dual;

If you have more mappings to make, you could use TRANSLATE ...

Hollingshead

Hi man, thanks for your answer, but my question is in general Á, É,.... á,é,...... How Can I do that? please – Wealth

As I indicated, use TRANSLATE if you have more mappings. That's what you want, to MAP 1 character to another. Trying to "remove accents" is a human thing, not a computer thing .. it doesn't understand that Ä and A look the same to us ;) so we have to tell it they're the same: ie a Mapping. TRANSLATE(data,'ÁÉáé...','AEae...') – Hollingshead

I don't think there is a general rule. For example in German you often translate Ä to AE, Ü to UE and Ö to OE if special characters are not available. – Stercoraceous

Not sure how many times I can restate something, but I'll try once more: TRANSLATE(data,'ÁÉáéÑ','AEaeN') That will map: Á to A, É to E, á to a, é to e, Ñ to N. Just keep adding the characters you need to map ... is there something about that you don't understand? or if it's not working, post what you tried, plus the error message. – Hollingshead

Answer 5

Good afternoon, I found this on this page https://blogs.oracle.com/sql/post/how-to-do-case-insensitive-and-accent-insensitive-search-in-oracle-database

select substr ( 
         athlete_name, 
         instr ( athlete_name, ', ' ) + 2 
       ) given_names,
       athlete_name
from olym_athletes
where athlete_name like '%helene%' 
         **Collate binary_ai**
order by given_names;   

using the collate binary_ai, and filters without uppercase and lowercase and also the accents I hope you serve another example

SELECT id, name, surname, salary
FROM EMPLOYEES
WHERE SURNAME LIKE '%PeRéZ%'
Collate binary_ai
;

Nitroparaffin

Answer 6

SELECT TRANSLATE('ÁSÚNCÍÓN','ÁÉÍÓÚ','AEIOU') "Traduccion" 
FROM dual;

Traduccion
----------
ASUNCION

Rowell

While this code may solve the question, including an explanation of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please edit your answer to add explanations and give an indication of what limitations and assumptions apply – Trihedron