select * from table1 where table1.id in ('1','2','3');
select * from table1 where table1.id in(replace('1#2#3,'#',''',''')) ??
替换后查不到数据,是不是方法有问题.该如何实现替换后面#为','
select * from table1 where table1.id in(replace('1#2#3,'#',''',''')) ??
替换后查不到数据,是不是方法有问题.该如何实现替换后面#为','
SQL> select replace('1#2#3','#',''',''') from dual;REPLACE('1#2#3','#
------------------
1','2','3
SQL> select replace(''''||'1#2#3'||'''','#',''',''') from dual;REPLACE(''''||'1#2#3'|
----------------------
'1','2','3'因此写成select * from table1
where table1.id in(replace(''''||'1#2#3'||'''','#',''','''));这样试试吧
select * from table1
where table1.id in(select replace(''''||'1#2#3'||'''','#',''',''') from dual);in里面并不会产生结果集,加个select,不过我认为这种做法不合理,为何要这样用.
你的rreplace('1#2#3,'#',''',''') 是不是少了什么东西?但是按你的思路,可能有两种解决办法
一、select * from table1 where replace(table1.id,'#','') in ('1','2','3')
二、select * from table1 where table1.id in ( select replace('1#2#3','#',''',''') from dual)
建议用动态SQL,然后用游标把查询结果再取出来
select * from t_test where id =substr('1#2#3',1,instr('1#2#3','#',1,1)-1)
union all
select * from t_test where id =substr('1#2#3',instr('1#2#3','#',1,1)+1,instr('1#2#3','#',1,2)-instr('1#2#3','#',1,1)
select * from t_test where instr('#'||'1#2#3'||'#','#'||id||'#')>0