oracle字符串多行分割

前言:对于单列字符串,怎么分割,这个大家都知道

select REGEXP_SUBSTR('01#02#03#04', '[^#]+', 1, rownum) as newport 
from dual connect by rownum <= regexp_count('01#02#03#04','#')+1;

接下来的2种扩展场景怎么处理呢?

场景1:单列多行字符串
如
aa,bb,cc
hh,jj
展示为:
aa,bb,cc aa
aa,bb,cc bb
aa,bb,cc cc
hh,jj hh
hh,jj jj

目前,有2种思路
第一种:创建方法,使用tab封装每一个结果集
如何获取tab
1.使用嵌套表
2.使用pip row

第二种:直接使用connect
这里有一个小技巧:
and(prior dbms_random.value) is not null,其作用是告诉oracle,自已跟自已递归是没有循环的

先看第一种方式

drop table   ysy_tab;
create table ysy_tab
as
select 'aa,bb,cc' tname from dual
union all
select 'dd,hh,jj' from dual
union all
select 'kk,ll,mm,uu' from dual;  
  
create or replace type  v_tab_type is table of varchar2(1000);  
create or replace function split_name(p_name varchar2,p_delim  varchar2 default ',')
  return v_tab_type deterministic is  
 v_tab v_tab_type;
  begin  
   select regexp_substr(p_name, '[^'|| p_delim|| ']+', 1, rownum) bulk collect into v_tab
    from dual
  connect by rownum <= regexp_count(p_name,p_delim) + 1;
   return v_tab;
  end; 

  
  select * from ysy_tab aa,table(split_name(aa.tname));
 
 
 --12c 支持临时方法 
 create or replace type  v_tab_type is table of varchar2(1000);  
 with function split_name(p_name varchar2,
 p_delim varchar2 default
  ',') return v_tab_type deterministic is v_tab v_tab_type;
 begin
   select regexp_substr(p_name, '[^' || p_delim || ']+', 1, rownum)
     bulk collect
     into v_tab
     from dual
   connect by rownum <= regexp_count(p_name, p_delim) + 1;
   return v_tab;
 end;
 select * from ysy_tab aa, table(split_name(aa.tname))
 /
 

再看第二种方式: 这就很简洁了

  select tname, regexp_substr(aa.tname, '[^,]+', 1, level) hh
    from ysy_tab aa
  connect by level <= regexp_count(aa.tname, ',') + 1
  and rowid=prior rowid
and(prior dbms_random.value) is not null;

场景2:多行多列拼接字符串
如:
drop table ysy_tab;
create table ysy_tab
as
select ‘aa$bb$cc’ tname,‘2012-01-01$2012-01-02$2012-01-03’ tdate from dual
union all
select ‘dd$hh$jj’ ,‘2012-01-01$2012-01-02$2012-01-03’ tdate from dual
union all
select ‘kk$ll’,‘2012-01-01$2012-01-02’ tdate from dual
union all
select ‘kk’,‘2012-01-05’ tdate from dual;

现在需要拆分,怎么处理呢?
方法1:
可以写个过程,每行单独处理完,insert,注意这里的$分隔符要转义

--用来存放分割之后的字符串
create table YSY_TAB2
(
  tname VARCHAR2(100),
  tdate VARCHAR2(100)
);


create or replace procedure proc_name_date
 is

   cursor c_type is
   select aa.tname,aa.tdate from ysy_tab aa where 1=2;
   type v_tab_type is table of c_type%rowType;
   v_tab v_tab_type;
 --用#拼接
   cursor c_get_info is
   select  aa.tname||'#'||aa.tdate tname from ysy_tab aa;


  v_name varchar2(120);
  v_date varchar2(100);
  v_index integer;
  p_delim varchar2(4):='\$';
  begin

   for v_row in c_get_info loop
      v_index:= instr(v_row.tname,'#');
      v_name:= substr(v_row.tname,0,v_index-1);
      v_date:=substr(v_row.tname,v_index+1);
      insert into ysy_tab2
        (tname, tdate)
        select regexp_substr(v_name, '[^' || p_delim || ']+', 1, rownum),
               regexp_substr(v_date, '[^' || p_delim || ']+', 1, rownum)
          from dual
        connect by rownum <= regexp_count(v_name, p_delim) + 1;
   end loop;
  end;

最后的效果如下:
这种适用于内存不足的情况

方法2:还是使用上面的方式2,一步到位
这种对内存使用高点

 select tname, regexp_substr(aa.tname, '[^\$]+', 1, level) h1,
        tdate,regexp_substr(aa.tdate, '[^\$]+', 1, level) h2
    from ysy_tab aa
   -- where aa.tname='aa$bb$cc'
  connect by level <= regexp_count(aa.tname, '\$') + 1
  and rowid=prior rowid
and(prior dbms_random.value) is not null;

(prior dbms_random.value) is not null的含义,可以参考:
http://blog.sina.com.cn/s/blog_4cef5c7b0102v2in.html