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

[toytony]

最近在研究字符转换时发现一个奇怪的问题,不多说,直接上SQL:

SQL

select 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'

Result

LEN CH2HEX HEX2CH COL2HEX COL HEX2COL CONTENT
60 D2B6 叶 53F6 叶 Sö 叶子 Ø12/Ø2


可以看到,同样都是"叶"字,只不过一个是我直接传入的,另一个是从数据中读出的,这有什么区别吗?

我在java中也测试过了,D2B6应该是正确的16位字符,不知道为什么从字段中读出的内容会解析出那么一个奇怪的结果.

PS:该字段是nvarchar的类型

请各位DX帮忙解惑,多谢多谢!!

[luyi6101]

 
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很奇怪

[toytony]

多谢支持.

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

[luyi6101]

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

[iqlife]

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

[iqlife]

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.ZHS16GBK

select  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

[toytony]

谢谢LS

我测试过HEXTORAW和UTL_RAW.CAST_TO_RAW,得到的结果是一样的.

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

[toytony]

回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

[iqlife]

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
结果是一样的

[iqlife]

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

一样的啊,兄弟

[toytony]

奇怪了,呵呵

select userenv('language') from dual; 

AMERICAN_AMERICA.ZHS16GBK

我的RAWTOHEX结果就是不一样,所以我才搞不懂了.晕呀....