关于char和varchar2的比较

char和varchar2比较

一些应注意的问题

关于char和varchar2的比较

char类型与char型或字符常量的比较，在比较时使用补齐空格的方式进行比较。
varchar2类型与varchar2类型，char型和字符常量的比较，在比较时不补充空格，直接比较。

create table tt(A1 CHAR(2) , A2 VARCHAR2(2)) ;
INSERT INTO TT VALUES('A','A') ;
insert into tt values('A','A ') ;
COMMIT ;

--CHAR型与字符常量的比较，字符常量作为char型处理
--与'A'比较，返回2行，也就是在比较时自动将常量'A'右补齐空格后比较
select * from tt where a1='A' ;
A1 A2
-- --
A  A
A  A

--与'A '比较，返回2行，也就是在比较时自动将常量'A'右补齐空格后比较
select * from tt where a1='A ' ;
A1 A2
-- --
A  A
A  A

--VARCHAR2与常量的比较，字符常量作为varchar2型处理
--与'A'比较，返回1行，也就是在比较时对'A'不做处理，直接比较
select  * from TT WHERE A2='A';
A1 A2
-- --
A  A
--与'A '比较，返回1行，也就是在比较时对'A'不做处理，直接比较
select  * from TT WHERE A2='A ';
A1 A2
-- --
A  A

--当CHAR类型和VARCHAR2类型比较时，
比较时对字段值是不作处理，直接比较的
--让A1和A2直接比较，此时是直接比较，有一条记录(第二条)的A1和A2相同
select * from tt where a1=a2 ;
A1 A2
-- --
A  A

但是当和decode函数配合使用时，出现不同的情况

使用A1字段
select  decode(a1,'A','AAAA','BBBB') FROM TT ;
DECODE(A1,'A','AAAA','BBBB')
----------------------------
BBBB
BBBB
虽然A1字段为char(2)，但是比较时并没有将常量'A'补空格再与字段A1做比较，
而是直接进行比较，也就是将两个比较字段按照varchar2类型处理的
因此比较时认为字段A1不等于常量'A'，出现两条结果为'BBB'的记录。

进一步验证，
select  decode(a1,'A  ','AAAA','BBBB') FROM TT ; --此处是两个空格
DECODE(A1,'A','AAAA','BBBB')
----------------------------
BBBB
BBBB
还是返回两条'BBB'的记录，说明比较的不是按照char型的比较规则处理的。

使用A2字段
select  decode(a2,'A','AAAA','BBBB') FROM TT ;
DECODE(A2,'A','AAAA','BBBB')
----------------------------
AAAA
BBBB
此时是正常的VARCHAR2类型之间的比较，第一条记录的A2字段等于'A'，返回'AAA',
第二行记录的A2字段为’A '，比较时不等，返回'BBB'

当使用case表达式处理A1字段时，出现了与decode函数不同的处理结果

使用字段A1
select  case a1 when 'A' then 'AAA' else 'BBB' end from tt ;
CASEA1WHEN'A'THEN'AAA'ELSE'BBB
------------------------------
AAA
AAA
在使用case语句中使用A1字段与常量'A'比较时，
两个比较值按照char型的比较规则处理，在右补空格之后进行比较，
因此返回两条记录

进一步验证，如下，
select  case a1 when 'A   ' then 'AAA' else 'BBB' end from tt ;
CASEA1WHEN'A'THEN'AAA'ELSE'BBB
------------------------------
AAA
AAA
此时将常量改为'A   '，比较时仍视为char类型之间的比较，将字段A1补齐空格后与常量比较。

使用A2字段
select  case a2 when 'A' then 'AAA' else 'BBB' end from tt ;
CASEA2WHEN'A'THEN'AAA'ELSE'BBB
------------------------------
AAA
BBB
此时是正常的VARCHAR2类型之间的比较，第一条记录的A2字段等于'A'，返回'AAA',第二行记录的A2字段为’A '，比较时不等，返回'BBB'

 

总结，
在使用decode函数(包括其它函数)对char字段做比较时，需要注意即使比较的两个字段都是char类型，
但是decode函数是将其转化varchar2类型 进行处理，不遵循char型的比较规则
而case为表达式，仍遵循char型的比较规则

另:
对于char数据，在集合操作中，按照char的实际数据进行比较，
而不是按照char型数据的比较规则进行的。

下面是一个简单例子
create table t1(name char(10)) ;
create table t2(name char(20)) ;
begin
for i in 1..5 loop
        insert into t1 values(to_char(i)) ;
        insert into t1 values(to_char(i)) ;
end loop ;
commit;
end ;

select name from t1 minus select name from t2 ;
NAME
--------------------
1
2
3
4
5
如果按照char型的比较规则，则不应该有返回值。

select anem from t1 intersect select name from t2 ;
NAME
--------------------

无返回值，也说明集合操作时比较没有按照char型的比较规则。