参数var_id 实际上是传进来的string ,
var_id :='123,456,789' ;select id, name from test_tbl where id in (var_id)
如何把in 里面的(‘123,456,789’)变成它能识别的类型(‘123’,‘456’,‘789’)?
var_id :='123,456,789' ;select id, name from test_tbl where id in (var_id)
如何把in 里面的(‘123,456,789’)变成它能识别的类型(‘123’,‘456’,‘789’)?
--id为字符类型
select id,name
from test_tb1
where id in(
substr(string,1,3),
substr(string,5,3),
substr(string,9,3));
--id为数字(number)类型
select id,name
from test_tb1
where id in(
to_number(substr(string,1,3)),
to_number(substr(string,5,3)),
to_number(substr(string,9,3)));
var_id varchar2(100) := '123,456,789';
begin
select '''' || replace(var_id, ',', ''',''') || ''''
into var_id
from dual;
dbms_output.put_line(var_id);
end;
--结果是'123','456','789'
replace掉不行吗?
把你的完整sql贴出来我看看
你换成instr试试
例子:
select *
from mw_app.mwt_gradeinfo g
where instr((select '''' || replace('123,456,789', ',', ''',''') || ''''
from dual),
g.stuname,
1,
1) > 0
换成 select * from mw_app.mwt_gradeinfo g
where instr((select '''' || replace('123,456', ',', ''',''') || '''' as newsubjects
from dual),
'''' || g.subject || '''',
1,
1) > 0;
这样就好了 匹配的时候 前后各加一个引号 就把12 23这种模糊匹配的数据过滤掉了