CODEWITHSUNDEEP
sqloracle-databasecharacter-set
Nimbocrux
Nimbocrux

Reputation: 519

Strip non English characters in Oracle SQL

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.

Upvotes: 0

Views: 2068

Answers (3)

Atif
Atif

Reputation: 2210

Apart from Translate you can also make use of Regular expression.Below post maybe helpful for you.

Finding and removing non ascii characters from an Oracle Varchar2

Upvotes: 0

PKey
PKey

Reputation: 3841

Try the translate function, it replaces a sequence of characters in a string with another set of characters, by replacing single character at a time.

e.g.

select translate(WORD,'ÃÉÄ','ΑΕA') from dual

or on your own example

select translate('JUAN ROMÄN', 'ÃÄ','AA') from dual;

Upvotes: 1

Orkad
Orkad

Reputation: 670

have you tried Translate() ?

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

Upvotes: 3

Related Questions