使用sql语句,首先将 A,B,C字符串分开,插入到一个临时表里面,然后使用临时表: 如下: 1、将A,B,C字符串分开插入到临时表t1中,假设t1表有字段v_cha。 select rownum,v_cha v_cha from t1;
rownum,v_cha 1 A 2 B 3 C2、然后你就可以使用上面的语句 select * from table where field1 in (select v_cha from t1);
直接用一个SQL语句如何实现?
虾米那是具体的操作语句: create temporary table t1 ( v_cha varchar2(10) ) on commit preserve rows; insert into t1(v_cha) with v_temp as ( select 'A,B,C' as v_cha from dual ) select decode(a, 0, substr(v_cha, b), substr(v_cha, b, a - b)) from ( select area_id, instr(v_cha, ',', 1, lv) a, lag(instr(v_cha, ',', 1, lv), 1, 0) over(order by lv) + 1 b from ( select t.v_cha , level as lv from v_temp t connect by level <= length(t.v_cha) - length(replace(t.v_cha, ',', '')) + 1 ) );
select * from table where field1 in (select v_cha from t1);
直接SQL实现恐怕有难度啊
用 instr 就可以了,不用弄什么临时表
declare -- Local variables here v_sql varchar2(1000); begin v_sql:='select * from table where field1 in (' || '''' || Replace(Trim(Upper('&a')), ',', ''',''') || ''')'; dbms_output.put_line(v_sql);
end;
str := 'A,B,C';SELECT * FROM TABLE WHERE field1 IN (SELECT regexp_substr(str, '[^,]+', 1, LEVEL) FROM t CONNECT BY LEVEL <= length(str) - length(REPLACE(str, ',')) + 1);
如下:
1、将A,B,C字符串分开插入到临时表t1中,假设t1表有字段v_cha。
select rownum,v_cha v_cha from t1;
rownum,v_cha
1 A
2 B
3 C2、然后你就可以使用上面的语句
select * from table where field1 in (select v_cha from t1);
(
v_cha varchar2(10)
)
on commit preserve rows;
insert into t1(v_cha)
with v_temp as
(
select 'A,B,C' as v_cha from dual
)
select decode(a, 0, substr(v_cha, b), substr(v_cha, b, a - b))
from
(
select area_id, instr(v_cha, ',', 1, lv) a,
lag(instr(v_cha, ',', 1, lv), 1, 0) over(order by lv) + 1 b
from
(
select t.v_cha , level as lv from v_temp t connect by level <=
length(t.v_cha) - length(replace(t.v_cha, ',', '')) + 1
)
);
select * from table where field1 in (select v_cha from t1);
declare
-- Local variables here
v_sql varchar2(1000);
begin
v_sql:='select * from table where field1 in (' || '''' || Replace(Trim(Upper('&a')), ',', ''',''') || ''')';
dbms_output.put_line(v_sql);
end;
FROM TABLE
WHERE field1 IN
(SELECT regexp_substr(str, '[^,]+', 1, LEVEL)
FROM t
CONNECT BY LEVEL <= length(str) - length(REPLACE(str, ',')) + 1);