CODEWITHSUNDEEP
sqloracle-database
GSandro_Strongs
GSandro_Strongs

Reputation: 901

how replace accented letter in a varchar2 column in oracle

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.

Upvotes: 16

Views: 38887

Answers (6)

Victor Muñoz
Victor Muñoz

Reputation: 1

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

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

Upvotes: 0

Leonardo Izquierdo
Leonardo Izquierdo

Reputation: 11

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
;

Upvotes: 0

Ankur Bhutani
Ankur Bhutani

Reputation: 3219

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

Upvotes: 32

Bacs
Bacs

Reputation: 919

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.

Upvotes: 15

Isaac
Isaac

Reputation: 33

You can use regular expressions:

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

Upvotes: 2

Ditto
Ditto

Reputation: 3344

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

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

Upvotes: 0

Related Questions