select substr (t.rpt_id,
instr (t.rpt_id,’,’,1,c.lv)+ 1,
instr (t.rpt_id,’,’,1,c.lv + 1)- (instr (t.rpt_id,’,’,1,c.lv)+ 1))
as rpt_id
from (select ‘,’ || ‘85,86,87’ || ‘,’ rpt_id,
length (‘85,86,87’ || ‘,’) – nvl (length (replace (‘85,86’, ‘,’)), 0) cnt
from dual) t,
(select level lv
from dual
connect by level <= length(‘85,86,87’ || ‘,’) – nvl(length(replace(‘85,86,87’, ‘,’)), 0)) c
where t.cnt >= c.lv
说明：cnt表示串里面有多少字符。
当是字符串是表中的字段时，取level<=最大个数
如：
１）.取最大个数
select max(length(rpt_id || ‘,’) –
nvl(length(replace(rpt_id, ‘,’)), 0)) into v_c
from dim_audit_table@sjmh_inter;
２）.l转成行
select t.t_name,
t.t_name_comm,
t.t_column,
t.t_column_comm,
t.comment_nl,
t.comment_nl_time,
t.seq_user_id,
substr(t.rpt_id,
instr(t.rpt_id, ”,”, 1, c.lv) + 1,
instr(t.rpt_id, ”,”, 1, c.lv + 1) –
(instr(t.rpt_id, ”,”, 1, c.lv) + 1)) as rpt_id
from (select a.t_name,
a.t_name_comm,
a.t_column,
a.t_column_comm,
a.comment_nl,
a.comment_nl_time,
a.seq_user_id,
”,” || a.rpt_id || ”,” rpt_id,
length(a.rpt_id || ”,”) –
nvl(length(replace(a.rpt_id, ”,”)), 0) cnt
from dim_audit_table@sjmh_inter a
where a.comment_nl is not null) t,
(select level lv from dual connect by level <= ‘||v_c||’) c
where c.lv <= t.cnt;
注:如果是上面代码是远程的代码,(select level lv from dual connect by level <= ‘||v_c||’) c,不要用dual表,可以改用all_objects或user_objects, 要不然能查询,但是把查询出来的sql插入到某个表时,只能插入一行