0

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

Michael O'Neill
  • 946
  • 7
  • 22
Vladut
  • 647
  • 1
  • 10
  • 35
  • 1
    What is the definition of the table, specifically the data type of the NAME column? – Michael O'Neill Feb 21 '18 at 14:03
  • @MichaelO'Neill VARCHAR2(1000 BYTE) – Vladut Feb 21 '18 at 14:09
  • 1
    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 Feb 21 '18 at 14:14

2 Answers2

2

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.

Michael O'Neill
  • 946
  • 7
  • 22
  • But I can to convert my inserted values (chinese characters in some characters from oracle) into AL16UTF16? – Vladut Feb 21 '18 at 14:21
  • You need to define your column data type as `NVARCHAR2(1000)` (not `VARCHAR2`), then you can insert Chinese characters. There is no conversion needed. – Wernfried Domscheit Feb 21 '18 at 14:52
  • I need to get my project name in chinese characters without change the settings into oracle. – Vladut Feb 21 '18 at 19:09
  • 1
    @Vladut Your *need* is unfortunately not enough to overcome the limitations stipulated. For solution, your only choices are to adapt your tools or reject your need. – Michael O'Neill Feb 21 '18 at 20:57
  • 1
    @WernfriedDomscheit Good point on conversion necessity. I updated the Answer. – Michael O'Neill Feb 21 '18 at 21:13
2

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') = '中文版测试';
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110