where_in_list问题

首先构造测试环境:

SCOTT@PROD>create table where_in_list(id int,name varchar(50));

Table created.

SCOTT@PROD>insert into where_in_list(id,name) values(1,'t1');

1 row created.

SCOTT@PROD>
SCOTT@PROD>insert into where_in_list(id,name) values(2,'t2');

1 row created.

SCOTT@PROD>
SCOTT@PROD>insert into where_in_list(id,name) values(3,'t3');

1 row created.

有时候我们会用到这样的查询:

SCOTT@PROD>select count(1) from where_in_list where name in ('t1','t2');

  COUNT(1)
----------
         2

但是当出现下面的情况的时候就会出现问题:

SCOTT@PROD>select count(1) from where_in_list where name in ('t1,t2');

  COUNT(1)
----------
         0

可以通过正则表达式解决这个问题:

SCOTT@PROD>var str varchar2(200);
SCOTT@PROD>exec :str:='t1,t2,t3';

PL/SQL procedure successfully completed.

SCOTT@PROD>
SCOTT@PROD>select count(1) from where_in_list
  2  where name in 
  3  (
  4   select regexp_substr(:str,'[^,]+',1,level) as value_str
  5    from dual
  6    connect by level <=
  7    length(trim(replace(:str,replace(:str,',',' '),' '))) +1
  8  );

  COUNT(1)
----------
         3