字符串分离
情形一:
select substr(t, 1, instr(t, ',', 1) - 1)
from (select substr(s, instr(s, ',', 1, rownum) + 1) as t,
rownum as d,
instr(s, ',', 1, rownum) + 1
from (select ',aa,bb,cc,' as s from dual)
connect by instr(s, ',', '1', rownum) > 1)
where t is not null
扩展:将下面左图的数据拆分成右边的数据格式
图1图2
图1部分的语句:
select ur.l_id, ur.vc_memberslist from team_info ur;
图2部分的语句:
select ur.l_id code, trim(regexp_substr(ur.vc_memberslist, '[^,]+', 1, level)) xxoo
from team_info ur
connect by prior ur.l_id = ur.l_id
and level <= regexp_count(ur.vc_memberslist, ',')
and prior dbms_random.value() is not null;
附建表语句:
create table team_info(
l_id number,
vc_memberslist varchar2(200)
);
comment on column TEAM_INFO.l_id
is '团队id';
comment on column TEAM_INFO.vc_memberslist
is '团队成员id';
insert into team_info (l_id, vc_memberslist)
values (645, '6503,560,319,7778,');
insert into team_info (l_id, vc_memberslist)
values (644, null);
insert into team_info (l_id, vc_memberslist)
values (647, null);
insert into team_info (l_id, vc_memberslist)
values (641, '555,666,');
insert into team_info (l_id, vc_memberslist)
values (621, '3590,400,666,7598,');
insert into team_info (l_id, vc_memberslist)
values (646, null);
情形二:
select regexp_substr('1,2', '[^,]+', 1, level)
from dual
connect by regexp_substr('1,2', '[^,]+', 1, level) is not null
上面的语句使用regexp_substr函数将带逗号的字符串根据逗号拆分成单个字符
这种写法还能嵌套在in的后面
select * from student
where sid in(select regexp_substr('1,2', '[^,]+', 1, level)
from dual
connect by regexp_substr('1,2', '[^,]+', 1, level) is not null);
上面这种写法会用在java后台的sql中传入带逗号的字符串(如:mybatis)
该技巧的搜索过程: