connect by 递归查询去分割! 建议你先去熟悉connect by 用法,就知道怎么去做事情啦!
select regexp_substr('1,2,3', '[^,]+', 1, level) from dual connect by level <= 3--结果 1 2 3
select regexp_substr('1,2,3', '[^,]+', 1, n) FROM dual left join (select level,rownum n from dual connect by level < 10) on 1=1 WHERE regexp_substr('1,2,3', '[^,]+', 1, n) IS not NULL;
建议你先去熟悉connect by 用法,就知道怎么去做事情啦!
connect by level <= 3--结果
1
2
3
FROM dual left join
(select level,rownum n from dual connect by level < 10) on 1=1
WHERE regexp_substr('1,2,3', '[^,]+', 1, n) IS not NULL;
str varchar2(32) := '1,2,3';
tmpstr varchar2(32);
pos1 number;
begin
while instr(str, ',', 1, 1) > 1 loop
pos1 := instr(str, ',', 1, 1);
tmpstr := substr(str, 1, pos1 - 1);
dbms_output.put_line(tmpstr);
str := substr(str, pos1 + 1);
end loop;
dbms_output.put_line(str);
end;
如果想在PL/SQL中实现:(即将一个字符串用分隔符拆分成数组)
请参考