Strip non English characters in Oracle SQL

Question

I'm working on a system that records persons names. We need to strip accents from the names to suit legacy systems. One such fictionalised example is RÃOUL TÉLITINO and we need to convert à to A and É to E and so forth.   I'm having difficulty doing so.   Information:  

SELECT *
  FROM v$nls_parameters
WHERE parameter LIKE '%CHARACTERSET'

Gives

**Parameter**:INLS_CHARACTERSET 
**Value**: AL32UTF8
   and
**Parameter**: NLS_NCHAR_CHARACTERSET     **Value**: AL16UTF16|

  What I have tried:

Collate

select 'RÃOUL TÉLITINO' collate SQL_Latin1_General_Cp1251_CS_AS

but I get

ORA-12746: unrecognied collation name "SQL_LATIN1_GENERAL_CP1251_CS_AS"

CONVERT()

First try

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

  Gives the desirable 'JUAN ROMAN', but

select CONVERT('RÃOUL TÉRK', 'US7ASCII') from dual;

misses the Ã, viz: R?OUL TERK.

second try

Other parameters I've tried are AL16UTF16 (but this doesn't change the input string) and AL16UTF16, AL24UTFFSS, AL32UTF8, but these convert to Chinese characters.

Answer 1

have you tried Translate() ?

translate(text,
'ÂÃÄÀÁÅÇÈÉÊËÌÍÎÏÑÒÓÔÕÖØÙÚÛÜÝŸàáâãäåçèéêëìíîïñòóôõöøùúûüýÿ',
'AAAAAACEEEEIIIINOOOOOOUUUUYYaaaaaaceeeeiiiinoooooouuuuyy')