oracle 将以逗号隔开的字符串'85,86,87' 转成行
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 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 where c.lv