17,382
社区成员




select id, substr(qx || ',', 1, instr(qx || ',', ',') - 1)
from user
union all
select id,
substr(replace(qx || ',',
substr(qx || ',', 1, instr(qx || ',', ',')),
''),
1,
instr(replace(qx || ',',
substr(qx || ',', 1, instr(qx || ',', ',')),
''),
',') - 1)
from user
union all
...
;
2)自定义函数分割,这个楼上几位已经有答案了。
create or replace function fn_convertcode(
p_instr in varchar2
) return varchar2 is
v_instr varchar2(100);
v_typeid varchar2(10);
v_typename varchar2(100);
v_result varchar2(100);
begin
v_instr := p_instr;
v_result := '';
loop
v_typeid := substr(v_instr, 1,
case when instr(v_instr, ',') > 0 then instr(v_instr, ',') - 1 else length(v_instr) end);
begin
select nvl(nr, 'unknow') into v_typename
from tba
where dm = v_typeid;
exception
when no_data_found then
v_typename := 'unknow';
end;
v_result := v_result || v_typename || ',';
exit when instr(v_instr, ',') = 0 or v_instr is null;
v_instr := substr(v_instr, instr(v_instr, ',') + 1);
end loop;
v_result := substr(v_result, 1, length(v_result) - 1);
return(v_result);
end fn_convertcode;