“¿” (inverted question mark) character in oracle

I found "¿" character (inverted question mark) in the database tables in place of single quote (') character.

我在数据库表中找到了“¿”字符(倒置问号)来代替单引号(')字符。

Can any one let me know how i can avoid this character from the table.

任何人都可以让我知道如何从表中避免这个角色。

There are many rows which contains the text with this character but not all single quotes are turning to this ¿ symbol.

有许多行包含带有此字符的文本,但并非所有单引号都转向此符号。

I am not even able to filter the rows to update this character (¿) with single quote again.

我甚至无法过滤行以再次使用单引号更新此字符(¿)。

When i user Like "%¿%" it filters me the text containing ordinary question mark (?)

当我的用户喜欢“%¿%”时,它会过滤包含普通问号的文字(?)

2 个解决方案

#1

In general there are two possibilities:

一般来说,有两种可能性:

Your database tables really have ¿ characters caused by wrong NLS_LANG settings when data was inserted (or the database character set does not support the special character). In such case the LIKE '%¿%' condition should work. However, this also means you have corrupt data in your database and it is almost impossible to correct them because¿ stands for any wrong character.

当插入数据时(或数据库字符集不支持特殊字符),您的数据库表确实具有由错误的NLS_LANG设置引起的字符。在这种情况下,LIKE'%¿%'条件应该有效。但是,这也意味着您的数据库中存在损坏的数据,并且几乎不可能纠正它们,因为它代表任何错误的字符。

Your client (e.g. SQL*Plus) is not able to display the special character caused by wrong NLS_LANG settings or the font does not support the special character.

您的客户端(例如SQL * Plus)无法显示由错误的NLS_LANG设置导致的特殊字符,或者字体不支持特殊字符。

Which client do you use (SQL*Plus, TOAD, SQL Developer, etc.)?

您使用哪个客户端(SQL * Plus,TOAD,SQL Developer等)?

What is your NLS_LANG Environment variable, resp. your Registry key HKLM\SOFTWARE\ORACLE\KEY_%ORACLE_HOME_NAME%\NLS_LANG or HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_%ORACLE_HOME_NAME%\NLS_LANG?

什么是你的NLS_LANG环境变量,分别是。您的注册表项HKLM \ SOFTWARE \ ORACLE \ KEY_%ORACLE_HOME_NAME%\ NLS_LANG或HKLM \ SOFTWARE \ Wow6432Node \ ORACLE \ KEY_%ORACLE_HOME_NAME%\ NLS_LANG?

What do you get when you select DUMP(... , 1016) from your table?

当你从桌子上选择DUMP(...,1016)时你会得到什么?

#2

Was it really a simple quote (ASCII 39)? If they actually were some sort of "smart quotes", those do have mappings in windows-1252 but there is no ISO-8859 mapping for them, so if your database charset is ISO-8859-1 and you try to insert some windows-1252 text, Oracle tries to translete from windows-1252 to ISO-8859-1 and uses chr(191) to signal an unmappable character. chr(191) happens to be the opening question mark.

它真的是一个简单的引用(ASCII 39)?如果它们实际上是某种“智能引号”,那些确实在windows-1252中有映射,但它们没有ISO-8859映射,所以如果你的数据库字符集是ISO-8859-1并且你试图插入一些窗口 - 在1252文本中,Oracle尝试从windows-1252转换为ISO-8859-1,并使用chr(191)来表示不可映射的字符。 chr(191)恰好是开头的问号。

You can check that by executing this (copy the select to preserve the smart quotes):

您可以通过执行此操作来检查(复制选择以保留智能引号):

select dump('‘’“”') from dual

This behaviour is basically "correct", as what you are asking Oracle to do cannot be done, though it is not very intuitive.

这种行为基本上是“正确的”,因为你要求Oracle做的事情无法完成,尽管它不是很直观。

See windows-1252, ISO8859-1 and ISO-8859-15 charsets for comparison. Note that windows uses the range 127-159 that is not used in the ISO charsets

有关比较,请参见windows-1252,ISO8859-1和ISO-8859-15字符集。请注意,Windows使用ISO字符集中未使用的范围127-159

#1