Converting German Characters into English within Oracle DB

Guys,

There is an example provided in 8i Documentation for Convert function.

SELECT CONVERT('Groß','US7ASCII', 'WE8HP') "Conversion" FROM DUAL;

Conversion
----------
Gross

But when you try to execute the same query on the DB. It does not return Gross instead it returns other characters.

Eg
GroO
Grou?
Gro??

and so on.

Is there any prerequisite to have a specific character set for the same. I have tried using WE8ISO8859P1, WE8MSWIN1252, UTF8 and US7ASCII
LVL 7
Mehul ShahIT consultantAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
schwertnerConnect With a Mentor Commented:
'Groß' can not be converted between US7ASCII and WE8HP.
The reason is that  US7ASCII can not represent 'ß'


WE8ISO8859P1 (ISO 8859-1):
 
Albanian, Basque, Breton, Catalan, Cornish, Danish, Dutch, Faroese, French,
Finnish, Frisian, Galician, German, Greenlandic, Icelandic, Irish Gaelic (new
orthography), Italian, Luxemburgish, Norwegian, Portuguese, Rhaeto-Romanic,
Scottish Gaelic, Spanish, Swedish
EE8ISO8859P2 (ISO 8859-2):
 
Albanian, Croatian, Czech, German, Hungarian, Polish, Slovak, Slovenian,  
Sorbian
 
SE8ISO8859P3 (ISO 8859-3):
 
Esperanto, French, German, Italian, Maltese, Portuguese, Turkish
 
NEE8ISO8859P4 (ISO 8859-4):
 
Danish, Estonian, Finnish, German, Greenlandic, Latvian, Lithuanian,
Norwegian, Sami, Slovenian, Swedish

UTF8 (in9i and 10g called AL32UTF8)
can store all character sets.

US7ASCII  can not store German letters.

0
 
riazpkConnect With a Mentor Commented:
From Documentation:

Purpose
CONVERT converts a character string from one character set to another. The datatype of the returned value is VARCHAR2.

The char argument is the value to be converted. It can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
The dest_char_set argument is the name of the character set to which char is converted.
The source_char_set argument is the name of the character set in which char is stored in the database. The default value is the database character set.
Both the destination and source character set arguments can be either literals or columns containing the name of the character set.

For complete correspondence in character conversion, it is essential that the destination character set contains a representation of all the characters defined in the source character set. Where a character does not exist in the destination character set, a replacement character appears. Replacement characters can be defined as part of a character set definition.

Examples
The following example illustrates character set conversion by converting a Latin-1 string to ASCII. The result is the same as importing the same string from a WE8ISO8859P1 database to a US7ASCII database.

SELECT CONVERT('Ä Ê Í Õ Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1')
   FROM DUAL;

CONVERT('ÄÊÍÕØABCDE'
---------------------
A E I ? ? A B C D E ?


Common character sets include:

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
0
 
Mehul ShahIT consultantAuthor Commented:
That I have already checked. But my question was that why the Oracle 8i example did not worked
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
riazpkCommented:
thats what i mentioned in my response:

<QUOTE>
For complete correspondence in character conversion, it is essential that the destination character set contains a representation of all the characters defined in the source character set. Where a character does not exist in the destination character set, a replacement character appears. Replacement characters can be defined as part of a character set definition.
</QUOTE>
0
 
Mehul ShahIT consultantAuthor Commented:
Yes I got your point riaz, I too tried doing all that. There are two things now

1) Yes US7ASCII does not store german letter. I dont want to store german letters in the US7ASCII database but I want to store the english equivalent of the same in the US7ASCII characterset. So Instead of "Groß" I want to store "Gross" into the US7ASCII database. While looking for that I found this example in Oracle 8i Documentation and so I tried using it but I could not do it.

2) If not this way is there any other way that we can convert all the german characters to its equivalent english characters with in the database itself. As I have a lot of data and manually translating it into english will be a hugh task. Is there a better way out. I was too happy to get the Convert function but when I tried implementing it :( it did not worked. Not sure why its mentioned in Oracle Documentation if that thing does not work at all.

I have checked in both 8i and 9i Databases with all diffirent charactersets but with no success.

As I mentioned before the requirement is to convert the German characters to its english equivalent that is

Groß to Gross

Also I did not understood the below line

"Replacement characters can be defined as part of a character set definition"

Does it mean that we can have specific replacement characters defined within a character set. If so how can I make oracle change ß to ss and so on.

0
 
Mehul ShahIT consultantAuthor Commented:
I am raising the request to close this  case. Please post your comments if you think I should not.
0
All Courses

From novice to tech pro — start learning today.