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