SQL题目8：拆分逗号分隔的字符串

-----第一种方法
-- 创建需要划分的字符串  
with T1 as(  
    select 1 as id ,'张三,李四,王五' as name from dual
    union all
    select 2 as id ,'a,b,c,d,e' as name from dual
     ),  
     
-- 统计字符串中子串的个数  
-- 字符串中','字符用''代替后，其减少的长度自然就是原串中','字符的个数  
T2 as(  
   select id,length(T1.name) - length(replace(T1.name, ',', '')) + 1  
          as source_substring_count  
     from T1),  
     
-- 根据子串的个数创建索引列，用于给T4的regexp_substr()方法索引  
T3 as(  
   select id,rownum as row_number  
      from dual, T2  
      
   connect by rownum <= T2.source_substring_count),  
     
-- 根据每个索引值逐个截取字符串  
T4 as(  
   select t1.id, T3.row_number as substring_index,  
          regexp_substr(T1.name, '[^,]+', 1, T3.row_number) as substring  
     from T1, T3)  
     
select id, substring_index, substring from T4
where substring is not null

-------第二种方法
with tt as
 (
 select 1 as id, '张三,李四,王五' || ',' as name
    from dual
  union all
  select 2, 'a,b,c,d,e' || ',' from dual
  ),
t1(id,
name,
lev) as
 (select id, substr(name, 1, instr(name, ',', 1, 1) - 1) as name, 1 as lev
    from tt
  union all
  select t.id,
         substr(t.name,
                instr(t.name, ',', 1, t1.lev) + 1,
                instr(t.name, ',', 1, t1.lev + 1) -
                instr(t.name, ',', 1, t1.lev) - 1) as name,
         lev + 1 as lev
    from t1
   inner join tt t
      on t1.id = t. id
     and instr(t.name, ',', 1, t1.lev + 1) > 0)
select id, name, lev from t1

------第三种方法
with tt as
 (select 1 as id, '张三,李四,王五' || ',' as name
    from dual
  union all
  select 2, 'a,b,c,d,e' || ',' from dual),
t1(id,
name,
lev) as
 (select id, regexp_substr(name, '[^,]+', 1, 1) as name, 1 as lev
    from tt
  union all
  select t.id,
         regexp_substr(t.name, '[^,]+', 1, lev + 1) as name,
         t1. lev + 1 as lev
    from t1
   inner join tt t
      on t1.id = t. id
     and instr(t.name, ',', 1, t1.lev+1) > 0)
select id, name, lev from t1