How to convert chinese characters to AL16UTF16 or WE8ISO8859P1?

0
votes

I have inserted into database some chinese characters. (Column name is NAME, data type is VARCHAR2) My project name is: 中文版测试 and I need to select project by this name.

But.

In oracle database are inserted 中文版测试 with name : ÖÐÎÄ°æ²âÊÔ (If I understand right my database has a set with the name WE8ISO8859P1)

I want to convert this characters from database (ÖÐÎÄ°æ²âÊÔ) to chinese characters (中文版测试) or to a same values to compare.

I try this:

select DIRNAME from MILLENNIUM.PROJECTINFO where UPPER(convert(NAME, 'AL32UTF8', 'we8iso8859p1')) = UPPER(convert('中文版测试', 'WE8MSWIN1252', 'AL32UTF8'));

I need to compare values from oracle with the name of the project.

Oracle settings: NLS_CHARACTERSET WE8ISO8859P1 0 NLS_NCHAR_CHARACTERSET AL16UTF16 0

2
databaseoracleoracle11g
What is the definition of the table, specifically the data type of the NAME column? – Michael O'Neill
@MichaelO'Neill VARCHAR2(1000 BYTE) – Vladut
Your assumption is incorrect that you inserted Chinese characters into a VARCHAR2 column of a database with those character set settings. WE8ISO8859P1 does not allow them. You want a table with a column with NVARCHAR2 to have your insert be something you can later work with the way you are trying to make comparisons. – Michael O'Neill

2 Answers

2
votes

You cannot take Chinese characters, insert them into a column that is bound by the WE8ISO8859P1 character set and then select them ever again as Chinese characters. You have lost information on your insert. That lost information cannot be reconstituted.

In your case, the NAME column if it were defined as NVARCHAR2, you could do a AL16UTF16 to AL16UTF16 comparison in a subsequent SELECT. Or, even better, not need to convert and compare with AL16UTF16 at all if your client tool is up to the task.

1
votes

AS Michael O'Neill already pointed out it is not possible to store Chinese characters in character set WE8ISO8859P1. All unsupported characters are automatically replaced by ¿ (or any other place holder)

BTW, WE8ISO8859P1 is different to WE8MSWIN1252 (see What is the exact difference between Windows-1252(1/3/4) and ISO-8859-1?), so your conversion does not work anyway.

Solution is to change data type of column NAME to NVARCHAR2 or migrate your database to UTF-8, see Character Set Migration and Database Migration Assistant for Unicode Guide. In any case you should consider your data being lost, resp. corrupted.

However, in case your client application was configured wrongly then in certain circumstances it is possible to insert unsupported characters, see If we have US7ASCII characterset why does it let us store non-ascii characters?.

In such case you can try to repair your data as this:

ALTER TABLE PROJECTINFO ADD NAME_CN NVARCHAR2(100);
UPDATE PROJECTINFO SET NAME_CN = UTL_I18N.RAW_TO_NCHAR(UTL_I18N.STRING_TO_RAW(NAME), 'ZHS16CGB231280');
ALTER TABLE PROJECTINFO DROP COLUMN NAME;
ALTER TABLE PROJECTINFO RENAME COLUMN NAME_CN TO NAME;

select DIRNAME from MILLENNIUM.PROJECTINFO where NAME = '中文版测试';

but it may not work for all of your data.

Hence a (not recommended) workaround for your problem could be

select DIRNAME 
from MILLENNIUM.PROJECTINFO 
where UTL_I18N.RAW_TO_NCHAR(UTL_I18N.STRING_TO_RAW(NAME), 'ZHS16CGB231280') = '中文版测试';