RAWTOHEX相同参数内容得到不同的结果

Question

最近在研究字符转换时发现一个奇怪的问题,不多说,直接上SQL:SQLselect lengthb(dscription1) Len,
RAWTOHEX('叶') Ch2Hex,
utl_raw.cast_to_varchar2(RAWTOHEX('叶')) Hex2Ch,
RAWTOHEX(substr(dscription1,1,1)) Col2Hex,
substr(dscription1,1,1) Col,
utl_raw.cast_to_varchar2(RAWTOHEX(substr(dscription1,1,1))) Hex2Col,
imdsc1 content
from  mytable 
where keyid = 'H76571' ResultLEN CH2HEX HEX2CH COL2HEX COL HEX2COL CONTENT             
60 D2B6 叶      53F6    叶      Sö      叶子 Ø12/Ø2      
可以看到,同样都是"叶"字,只不过一个是我直接传入的,另一个是从数据中读出的,这有什么区别吗?我在java中也测试过了,D2B6应该是正确的16位字符,不知道为什么从字段中读出的内容会解析出那么一个奇怪的结果.PS:该字段是nvarchar的类型请各位DX帮忙解惑,多谢多谢!!

Answer 1

 
SQL> create table ccc(name nvarchar2(222));
 
Table created
 
SQL> insert into ccc values('叶');
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> 
SQL> select lengthb(name) Len,
  2  RAWTOHEX('叶') Ch2Hex,
  3  utl_raw.cast_to_varchar2(RAWTOHEX('叶')) Hex2Ch,
  4  RAWTOHEX(substr(name,1,1)) Col2Hex,
  5  substr(name,1,1) Col,
  6  utl_raw.cast_to_varchar2(RAWTOHEX(substr(name,1,1))) Hex2Col
  7  from  ccc
  8  /
 
       LEN CH2HEX HEX2CH                                                                           COL2HEX COL HEX2COL
---------- ------ -------------------------------------------------------------------------------- ------- --- --------------------------------------------------------------------------------
         2 3F     ?                                                                                003F    ?   
 
SQL> 
SQL> select lengthb(name) Len,
  2  RAWTOHEX('叶') Ch2Hex,
  3  utl_raw.cast_to_varchar2(RAWTOHEX('叶')) Hex2Ch,
  4  RAWTOHEX(name) Col2Hex,
  5  substr(name,1,1) Col,
  6  utl_raw.cast_to_varchar2(RAWTOHEX(substr(name,1,1))) Hex2Col
  7  from  ccc
  8  
SQL> /
 
       LEN CH2HEX HEX2CH                                                                           COL2HEX COL HEX2COL
---------- ------ -------------------------------------------------------------------------------- ------- --- --------------------------------------------------------------------------------
         2 3F     ?                                                                                003F    ?   
 
SQL> select case when RAWTOHEX('叶')=RAWTOHEX(substr(name,1,1)) then 1 else 0 end from ccc;
 
CASEWHENRAWTOHEX('?')=RAWTOHEX
------------------------------
                             0
 
SQL> select case when to_number(RAWTOHEX('叶'),'XXX')=to_number(RAWTOHEX(substr(name,1,1)),'XXX') then 1 else 0 end from ccc;
 
select case when to_number(RAWTOHEX('?'),'XXX')=to_number(RAWTOHEX(substr(name,1,1)),'XXX') then 1 else 0 end from ccc
 
ORA-01722: ÎÞЧÊý×Ö
 
SQL>  select case when to_number(RAWTOHEX('叶'),'XXX')=to_number(RAWTOHEX(substr(name,1,1)),'XXXX') then 1 else 0 end from ccc;
 
CASEWHENTO_NUMBER(RAWTOHEX('?'
------------------------------
                             1
 
SQL> drop table ccc;
 
Table dropped
 
SQL> oracle在底层都是2进制来判断的，自动在前面补0很奇怪

Answer 2

多谢支持.发现之前我写错了一个地方,字段是nchar,不是nvarchar....抱歉抱歉!!

Answer 3

你自己改成nchar试试，nchar（？）

Answer 4

当使用HEXTORAW时，会把字符串中数据当作16进制数。而使用UTL_RAW.CAST_TO_RAW时，直接把字符串中每个字符的ASCII码存放到RAW类型的字段中

Answer 5

select lengthb(a.sname) Len, 
RAWTOHEX('叶') Ch2Hex, 
utl_raw.cast_to_varchar2(RAWTOHEX('叶')) Hex2Ch, 
RAWTOHEX(substr(a.sname,1,1)) Col2Hex, 
substr(a.sname,1,1) Col, 
utl_raw.cast_to_varchar2(RAWTOHEX(substr(a.sname,1,1))) Hex2Col
from  a
where a.id=7
--结果:
LEN CH2HEX HEX2CH COL2HEX COL HEX2COL
8 D2B6 叶 D2B6 叶 叶a中数据:叶子黄了,无你说的现象,查看字符集:
select userenv('language') from dual; 
USERENV('LANGUAGE')
SIMPLIFIED CHINESE_CHINA.ZHS16GBKselect  from v$nls_parameters;
PARAMETER VALUE
NLS_LANGUAGE SIMPLIFIED CHINESE
NLS_TERRITORY CHINA
NLS_CURRENCY RMB
NLS_ISO_CURRENCY CHINA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE SIMPLIFIED CHINESE
NLS_CHARACTERSET ZHS16GBK
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY RMB
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

Answer 6

谢谢LS我测试过HEXTORAW和UTL_RAW.CAST_TO_RAW,得到的结果是一样的.我希望得到的结果是HEXTORAW('叶')和HEXTORAW(substr(dscription1,1,1))能得到相同的结果,否则就没有意义了呀,我总不可能在程序中都写固定的内容呀.

Answer 7

回5楼:我的字符集和你有点区别,关键是国家码不一样,但是这个是不允许修改的.
select 'server',nls_database_parameters.* from nls_database_parameters
union
select 'client',nls_instance_parameters.* from nls_instance_parameters
'SERVER' PARAMETER VALUE
client NLS_CALENDAR
client NLS_COMP
client NLS_CURRENCY
client NLS_DATE_FORMAT
client NLS_DATE_LANGUAGE
client NLS_DUAL_CURRENCY
client NLS_ISO_CURRENCY
client NLS_LANGUAGE AMERICAN
client NLS_LENGTH_SEMANTICS BYTE
client NLS_NCHAR_CONV_EXCP FALSE
client NLS_NUMERIC_CHARACTERS
client NLS_SORT
client NLS_TERRITORY AMERICA
client NLS_TIMESTAMP_FORMAT
client NLS_TIMESTAMP_TZ_FORMAT
client NLS_TIME_FORMAT
client NLS_TIME_TZ_FORMAT
server NLS_CALENDAR GREGORIAN
server NLS_CHARACTERSET ZHS16GBK
server NLS_COMP BINARY
server NLS_CURRENCY $
server NLS_DATE_FORMAT DD-MON-RR
server NLS_DATE_LANGUAGE AMERICAN
server NLS_DUAL_CURRENCY $
server NLS_ISO_CURRENCY AMERICA
server NLS_LANGUAGE AMERICAN
server NLS_LENGTH_SEMANTICS BYTE
server NLS_NCHAR_CHARACTERSET AL16UTF16
server NLS_NCHAR_CONV_EXCP FALSE
server NLS_NUMERIC_CHARACTERS .,
server NLS_RDBMS_VERSION 10.2.0.2.0
server NLS_SORT BINARY
server NLS_TERRITORY AMERICA
server NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
server NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
server NLS_TIME_FORMAT HH.MI.SSXFF AM
server NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR

Answer 8

select RAWTOHEX(substr('叶',1,1)),RAWTOHEX(substr(a.sname,1,1)) Hex2Col from a
where a.id=7
RAWTOHEX(SUBSTR('叶',1,1)) HEX2COL
D2B6 D2B6
结果是一样的

Answer 9

select * from nls_database_parameters order by parameter;
PARAMETER VALUE
NLS_CALENDAR GREGORIAN
NLS_CHARACTERSET ZHS16GBK
NLS_COMP BINARY
NLS_CURRENCY $
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_DUAL_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_LANGUAGE AMERICAN
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS .,
NLS_RDBMS_VERSION 9.2.0.1.0
NLS_SORT BINARY
NLS_TERRITORY AMERICA
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
一样的啊,兄弟

Answer 10

奇怪了,呵呵
select userenv('language') from dual; AMERICAN_AMERICA.ZHS16GBK
我的RAWTOHEX结果就是不一样,所以我才搞不懂了.晕呀....