在我们平时的处理过程总往往会遇到要把一个字符串拆分成一个数组,加在'in'后面作为条件,现提供两种方似乎。
1- 正则表达式分割字符串
select regexp_substr('2,3,4,5', '[^,]+', 1, l_count) stype from dual,
(select level l_count from dual connect by level <= length('2,3,4,5')) ;
2- 用REPLACE实现
--处理公用条件
v_custnos := '''' || REPLACE (p_custno, ',', ''',''') || '''';
--组装SQL
v_accosql :=
'select c_custno,c_fundacco,c_custname,d_date,d_cdate,null c_fundcode,
c_agencyno,c_businflag,fget_businflag(c_businflag) c_businname,0 f_balance,0 f_shares,
0 f_confirmbalance,0 f_confirmshares,0 f_netvalue,
c_status,c_cause,c_tradeacco,''1'' as c_dataflag,
l_serialno,null c_cserialno,null c_netno,null c_tano,null l_proserialno,null c_profitclass
from taccorequest t where c_custno in ('
|| v_custnos
|| ')':
select substr('22,2,3',1,INSTR('22,2,3',',',1,1)-1) from dual; --截取第一个
select substr('22,2,3',INSTR('22,2,3',',',1,1)+1,(INSTR('22,2,3',',',1,2)-INSTR('22,2,3',',',1,1)-1)) from dual; --截取第二个
select substr('22,2,3',INSTR('22,2,3',',',1,2)+1,(length('22,2,3')-INSTR('22,2,3',',',1,2))) from dual; --截取第三个