Oracle sql技巧--字符串分离

字符串分离

情形一：

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）

该技巧的搜索过程：