有朋友提出如下问题
/*为什么这样不行*/
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>