--这样的问题,好像问过多次了吧 with tab as( select '群组名称1;群组名称2;群组名称3;' id from dual ) select substr(';'||id,instr(';'||id,';',1,level)+1, instr(';'||id,';',1,level+1)-instr(';'||id,';',1,level)-1) newid from tab connect by level <= length(';'||id) - length(replace(';'||id, ';', ''))-1 --10g,用正则表达式简单些: with tab as( select '群组名称1;群组名称2;群组名称3;' id from dual ) select regexp_substr(id,'[^;]+',1,level) as newid from tab connect by level<=length(id)-length(replace(id,';',''))
str varchar2(1000);
strTemp varchar2(100); --每个分号前得字符串
semi_num int; --分号个数
pos int;
semi_num := 0;
str := 你的字符串;
pos := instr(str,';');
while pos > 0
loop
strTemp := substr(str,1,pos-1);
str := substr(str,pos+1);
pos := instr(str,';');
semi_num = semi_num + 1;
end loop;
...
with tab as(
select '群组名称1;群组名称2;群组名称3;' id from dual
)
select substr(';'||id,instr(';'||id,';',1,level)+1,
instr(';'||id,';',1,level+1)-instr(';'||id,';',1,level)-1) newid
from tab
connect by
level <= length(';'||id) - length(replace(';'||id, ';', ''))-1
--10g,用正则表达式简单些:
with tab as(
select '群组名称1;群组名称2;群组名称3;' id from dual
)
select regexp_substr(id,'[^;]+',1,level) as newid
from tab
connect by
level<=length(id)-length(replace(id,';',''))