这个就直接把答案写在下面,想自己做的小伙伴可以不看下面的答案,自己尝试着先做一下
ID | NAME |
1 | '张三,李四,王五' |
2 | 'a,b,c,d,e' |
ID | NAME |
1 | 张三 |
1 | 李四 |
1 | 王五 |
2 | a |
2 | b |
2 | c |
2 | d |
2 | e |
话不多说,直接上代码,想做题的小伙伴就不用往下看了,直接关注我,想看答案的可以继续~
刚开始编写文章,很多地方都有不足,希望以后可以为大家贡献出更好的文章,谢谢大家!有哪里不明白的地方可以和作者联系〜
没有关注的也可以关注下公众号〜再次感谢
-----第一种方法-- 创建需要划分的字符串 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 T4where 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
今天就到这里,有什么不明白可以联系我~~~