在oracle中，utf-8到utf-16的转换

I actually stored resources in DB in UTF-8 format. But when I want to convert them all into UTF-16 now. As the german language is having some characters like 1/4. Now I want to avoid those. I have tried with following the statement, but got some boxes in result string....

我实际上以UTF-8格式将资源存储在DB中。但是现在我想把它们都转换成UTF-16。因为德语有一些字符，比如1/4。现在我想避免这些。与声明后,我试过,但结果字符串中有一些盒子....

> select convert('Inhalt hinzufÃ¼gen','AL16UTF16LE','AL32UTF8') from dual
  result : it is not allowing me to copy paste it :(. But result is coming properly except boxes in middle of each character

is there any alternative approach?

有没有其他的方法?

SELECT *
  FROM v$nls_parameters
 WHERE parameter LIKE '%CHARACTERSET';

indicates that my database character set is WE8MSWIN1252 while my national character set is AL32UTF16.

指示我的数据库字符集是WE8MSWIN1252，而我的国家字符集是AL32UTF16。

When I use the DUMP function to view the data that is actually stored in my table, this is the output:

当我使用DUMP函数查看表中实际存储的数据时，这是输出:

SELECT dump( your_column, 1016 ), your_column
  FROM your_table
 WHERE some_key_column = <<value that gives you the row you're interested in>>

Typ=1 Len=54 CharacterSet=WE8MSWIN1252: 4d,c3,b6,63,68,74,65,6e,20,53,69,65,20,64,69,65,73,65,20,5a,65,69,6c,65,20,77,69‌,72,6b,6c,69,63,68,20,65,6e,64,67,c3,bc,6c,74,69,67,20,6c,c3,b6,73,63,68,65,6e,3f, MÃ¶chten Sie diese Zeile wirklich endgÃ¼ltig lÃ¶schen?

Typ = 1 Len = 54 CharacterSet = WE8MSWIN1252:4 d,c3,b6,63年,68年,74年,65年,6 e,20日,53岁,69年,65年,20年,64年,69年,65年,73年,65年,20岁,5,65年,69年,6 c,65年,20年,77年,69年‌,72,6 b、6 c,69年,63年,68年,20岁,65年6 e,64年,67年,c3,公元前6 c,74年,69年,67年,20日6 c,c3,b6,73,63,68,65,6 e,3 f,马英九¶chten您这Zeile wirklich endgA¼ltig lA¶schen吗?

1 个解决方案

#1

Since your database character set is WE8MSWIN1252, your data is hopefully not actually stored as UTF-8. If the actual data is being stored in a CHAR, VARCHAR2, or CLOB column, the data is either stored using Windows-1252 character set or the data has been stored incorrectly. It is possible that you have configured your NLS environment incorrectly so that you're actually storing UTF-8 data in the database but hopefully not the case here.

由于您的数据库字符集是WE8MSWIN1252，所以您的数据很有可能不是作为UTF-8存储的。如果实际数据存储在CHAR、VARCHAR2或CLOB列中，则数据要么使用Windows-1252字符集存储，要么数据被错误存储。可能您已经错误地配置了您的NLS环境，因此您实际上正在数据库中存储UTF-8数据，但希望不是这里的情况。

Based on the output of the DUMP function, what character do you expect to be stored in the third position of the data? 0xB6 is the data that is actually stored in the database which maps to the paragraph symbol ¶ in the Windows-1252 character set. Assuming that is not the character that you expect, it would appear that the data that is stored in the database has been corrupted.

根据DUMP函数的输出，您希望将哪个字符存储在数据的第三个位置?0 xb6是实际的数据存储在数据库中,映射到段象征¶在windows - 1252字符集。假设不是你期望的角色,看起来数据库中存储的数据已被损坏。

What language(s) is your data written in? Are all the characters that you want to store present in the Windows-1252 character set?

您的数据是用什么语言编写的?您想要存储在Windows-1252字符集中的所有字符吗?

Are you trying to change how the data is stored? Or are you trying to retrieve the data in a different character set?

您是否正在尝试更改数据的存储方式?或者您正在尝试在另一个字符集中检索数据?

If the database character set is AL32UTF8, the national character set is AL32UTF16, and you want to store the data in the database using UTF-16, you'd need to move the data into a NVARCHAR2 or NCLOB column.

如果数据库字符集是AL32UTF8，则国家字符集是AL32UTF16，并且您希望使用UTF-16在数据库中存储数据，您需要将数据移动到NVARCHAR2或NCLOB列。

If you are trying to store the data in a UTF-8 format in the database but then send it to the client in UTF-16, that can be done automatically by configuring the client's NLS settings. Exactly how you do that will depend on how the client accesses the database (JDBC, ODBC, etc.).

如果您试图在数据库中以UTF-8格式存储数据，然后以UTF-16格式发送给客户端，可以通过配置客户端NLS设置自动完成。具体的实现方式将取决于客户端访问数据库的方式(JDBC、ODBC等)。

#1