char与相等判断

有朋友提出如下问题

/*为什么这样不行*/
select * from a1,a2 where a1.name=replace(a2.name,CHR(13),'');

/*这样就可以*/
select * from a1,a2 where a1.name=replace(a2.name,CHR(13),CHR(32));

他的案例环境如下

DROP TABLE a1 PURGE;
DROP TABLE a2 PURGE;
create table a1(id int,name char(10));
create table a2(id int,name char(10));
insert into a1 values(1,'a');
insert into a2 values(100,'a'||CHR(13));
commit;

我们来分析一下原因，首先看下a2表替换后的值

SQL> select length('|' || replace(a2.name,CHR(13),'') || '|') AS L2,'|' || replace(a2.name,CHR(13),'') || '|' AS a2_n from a2;
        L2 A2_N
---------- ------------
        11 |a        |

这儿长度是11。

再看下a1表

SQL> select length('|' || name || '|') AS L1,'|' || name || '|' AS a1_n from a1;
        L1 A1_N
---------- ------------
        12 |a         |

长度是12。

a2表少了一位，是因为其中的chr(13)被替换掉了。

而两个表的字段都是char（定长），这样作判断肯定是不相等了。a2.name替换后需要加上一位空格来补齐

正确语句应该如下

SQL> select * from a1,a2 where a1.name=replace(a2.name,CHR(13),'') || ' ';
                                     ID NAME                                            ID NAME
--------------------------------------- ---------- --------------------------------------- ----------
                                      1 a                                              100 a

如果字段类型改为变长：varchar2，就没有这种问题了。

SQL> DROP TABLE a1 PURGE;
Table dropped
SQL> DROP TABLE a2 PURGE;
Table dropped
SQL> create table a1(id int,name VARCHAR2(10));
Table created
SQL> create table a2(id int,name VARCHAR2(10));
Table created
SQL> insert into a1 values(1,'a');
1 row inserted
SQL> insert into a2 values(100,'a'||CHR(13));
1 row inserted
SQL> commit;
Commit complete
SQL> select * from a1,a2 where a1.name=replace(a2.name,CHR(13),'');
                                     ID NAME                                            ID NAME
--------------------------------------- ---------- --------------------------------------- ----------
                                      1 a                                              100 a

SQL>