3

I want to replace all German characters of a column of a table to corresponding English characters. When I tried with Replace() function it is not returning fruitful result. I want to replace all German special characters like- Ä Ö Ü ö ä ü ß to Ae Oe Ue oe ae ue ss. Please let me know how to perform that? Do I need to change any DB settings?

Please find some results below:

select replace('a b c d e ä f g ö','ä','ae') from dual;
Output:


REPLACE('ABCDEDFGV','D','AE')
a b c ae e ae f g v

I am using Toad as DB tool. below are few DB parameters:

PARAMETER VALUE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8ISO8859P1
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE GERMAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_RDBMS_VERSION 9.2.0.5.0
NLS_SAVED_NCHAR_CS WE8ISO8859P1
Dip
  • 33
  • 1
  • 4
  • 1
    Your 'output' seems to be from a different query. It would help to show corresppnding queries and results. – Alex Poole Nov 18 '15 at 12:14

3 Answers3

4

Your terminal character set does not match your NLS_LANG character set. They have to match. You can do it like this:

C:\>chcp
Active code page: 850

C:\>set NLS_LANG=.WE8PC850

C:\>sqlplus user/pwd@DB

SQL> select replace('a b c d e ä f g ö','ä','ae') from dual;

When you work on Linux/Unix the chcp equivalent is locale charmap or echo $LANG. You can also change current code page, e.g. chcp 65001 for UTF-8.

Some common pairs are:

  • Codepage 850: (the windows default) chcp 850 -> WE8PC850

  • Codepage 1252: chcp 1252 -> WE8MSWIN1252

  • ISO-8859-1: chcp 28591 -> WE8ISO8859P1

  • ISO-8859-15: chcp 28605 -> WE8ISO8859P15

  • UTF-8: chcp 65001 -> AL32UTF8

It does not matter which way you use, either change NLS_LANG according your terminal character set or vice versa. By NLS_LANG=.WE8PC850 you do not overwrite your current language and territory. Of course you can also do NLS_LANG=GERMAN_GERMANY.WE8PC850

In your case chcp 28591 -> WE8ISO8859P1 might be the "best", because then you can work with any character which is supported by your database. If you use another setting, e.g. "Codepage 1252" you may get some unsupported characters, e.g. ¿ for €, because WE8ISO8859P1 does not support the Euro currency symbol.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
1

What's wrong with the result? It replaces ä with ae. Now repeat for all other characters.... Since replace will only replace 1 sequence with another you will get a lot of nested replaces.

select replace(replace('a b c d e ä f g ö','ä','ae'),'ö','oe') from dual

etc.

Rene
  • 10,391
  • 5
  • 33
  • 46
1

From OTN, "replace characters with accent with their base letter":

with demo as (
    select 'a b c d e ä f g ö' as de from dual
)
select de
     , utl_raw.cast_to_varchar2(nlssort(de, 'nls_sort=binary_ai')) as en
from   demo;

Explanation: nlssort() returns the binary representation of the character as used for sorting, which is the base character you want. utl_raw.cast_to_varchar2 converts the binary version to a string.

Edit: Hmm, as Alex has pointed out, this isn't quite what the OP asked for. Sorry.

Also the results will depend on the globalization settings for the database, the desktop and the application, and differences could appear when I copy sample text off a web browser, when I paste that text into an application and when the application displays results. I get the expected base characters in PL/SQL Developer 11 with NLS_CHARACTERSET = AL32UTF8 etc, but not in SQL*Plus 11.2 on the same UK Windows desktop.

William Robertson
  • 15,273
  • 4
  • 38
  • 44