Oracle SQL多行字符串拆分使用层次查询(connect by)、正则表达式

经常会遇到字符串拆分，比如逗号分隔、空格分隔、没有分隔符（按指定长度拆分）等类型的字符串拆分。由于这几种类型的处理思路是一致的，下面就逗号分隔的情况做下分析：

下面展示下我经常使用的方法：

层次查询+正则表达式

单行的情况：select regexp_substr('a,bb,ccc,bb', '[^,]+', 1, level) split_chr  from dualconnect by regexp_instr('a,bb,ccc,bb', '[^,]+', 1, level) > 0; 或者select regexp_substr('a,bb,ccc,bb', '[^,]+', 1, level) split_chr from dualconnect by regexp_substr('a,bb,ccc,bb', '[^,]+', 1, level) is not null;SPLIT_CHR----------------------abbcccbb步骤分析：select 'a,bb,ccc,bb' chr, level  from dualconnect by regexp_instr('a,bb,ccc,bb', '[^,]+', 1, level) > 0;CHR              LEVEL-----------                   ----------a,bb,ccc,bb          1a,bb,ccc,bb          2a,bb,ccc,bb          3a,bb,ccc,bb          4单行的情况下，其实是通过层次查询connect by，复制多行数据数据出来，结合伪列level，进行截取。这棵树不会分叉，所以比较容易处理。

多行的情况with t as(select 'a,bb,ccc,bb' chr from dual union all select 'e,ff,ggg,ff,xx' from dual)select regexp_substr(t.chr, '[^,]+', 1, level) split_chr  from tconnect by regexp_instr(t.chr, '[^,]+', 1, level) > 0;此时结果出现了错误，因为套用单行处理逻辑，看下下面这条语句就能明白出错的原因。我们只用3个逗号分隔的字符来模拟with t as(select 'a,bb,ccc' chr from dual union all select 'e,ff,ggg' from dual)select regexp_substr(t.chr, '[^,]+', 1, level) split_chr,level,connect_by_root chr root_chr,sys_connect_by_path(chr,'/') path  from tconnect by regexp_instr(t.chr, '[^,]+', 1, level) > 0;SPLIT_CHR       LEVEL   ROOT_CHR    PATHa                1    a,bb,ccc    /a,bb,cccbb               2    a,bb,ccc    /a,bb,ccc/a,bb,cccccc              3    a,bb,ccc    /a,bb,ccc/a,bb,ccc/a,bb,cccggg              3    a,bb,ccc    /a,bb,ccc/a,bb,ccc/e,ff,gggff               2    a,bb,ccc    /a,bb,ccc/e,ff,gggccc              3    a,bb,ccc    /a,bb,ccc/e,ff,ggg/a,bb,cccggg              3    a,bb,ccc    /a,bb,ccc/e,ff,ggg/e,ff,ggge                1    e,ff,ggg    /e,ff,gggbb               2    e,ff,ggg    /e,ff,ggg/a,bb,cccccc              3    e,ff,ggg    /e,ff,ggg/a,bb,ccc/a,bb,cccggg              3    e,ff,ggg    /e,ff,ggg/a,bb,ccc/e,ff,gggff               2    e,ff,ggg    /e,ff,ggg/e,ff,gggccc              3    e,ff,ggg    /e,ff,ggg/e,ff,ggg/a,bb,cccggg              3    e,ff,ggg    /e,ff,ggg/e,ff,ggg/e,ff,ggg我们可以看到，在root_chr相等的情况下，说明他们是从相同的根节点出来的子节点，但是问题出现在，到了level=2的时候，这棵树分叉了，level=3在level=2的基础上又分叉了。可实际情况是我们期望每个根节点只需要复制出（逗号个数+1）条记录来，再结合level做截取。针对以上情况，有两种处理办法。第一种：使用distinct去重，但是由于逗号分隔的字符串也可能是相同的，所以对我们的测试数据来说，结果会缺失，比如结果应该是9条，但是去重后是7条。with t as(select 'a,bb,ccc,bb' chr from dual union all select 'e,ff,ggg,ff,xx' from dual)select distinct regexp_substr(t.chr, '[^,]+', 1, level) split_chr  from tconnect by regexp_instr(t.chr, '[^,]+', 1, level) > 0;SPLIT_CHR---------ebbxxgggaffccc7 rows selected.第二种：主要是使用sys_connect_by_path，这样就可以知道每个节点的所有上级节点，只保留所有节点都一样的那个分支。  select chr, subchr,path  from (select chr,               sys_connect_by_path(chr, '\') path,               regexp_substr(t.chr, '[^,]+', 1, level) subchr          from ((select 'a,bb,ccc,bb' chr from dual union all select 'e,ff,ggg,ff,xx' from dual)) t        connect by level <= 5               and regexp_instr(t.chr, '[^,]+', 1, level) > 0) where regexp_substr(path, '^(\\[^\\]+)\1{0,4}$') is not null;CHR                SUBCHR   PATH--------------            --------  --------------------------------------------------------------------------------a,bb,ccc,bb            a     \a,bb,ccc,bba,bb,ccc,bb            bb     \a,bb,ccc,bb\a,bb,ccc,bba,bb,ccc,bb            ccc    \a,bb,ccc,bb\a,bb,ccc,bb\a,bb,ccc,bba,bb,ccc,bb            bb     \a,bb,ccc,bb\a,bb,ccc,bb\a,bb,ccc,bb\a,bb,ccc,bbe,ff,ggg,ff,xx            e      \e,ff,ggg,ff,xxe,ff,ggg,ff,xx            ff     \e,ff,ggg,ff,xx\e,ff,ggg,ff,xxe,ff,ggg,ff,xx            ggg    \e,ff,ggg,ff,xx\e,ff,ggg,ff,xx\e,ff,ggg,ff,xxe,ff,ggg,ff,xx            ff     \e,ff,ggg,ff,xx\e,ff,ggg,ff,xx\e,ff,ggg,ff,xx\e,ff,ggg,ff,xxe,ff,ggg,ff,xx            xx     \e,ff,ggg,ff,xx\e,ff,ggg,ff,xx\e,ff,ggg,ff,xx\e,ff,ggg,ff,xx\e,ff,ggg,ff,xx9 rows selected.虽然还是感觉有的复杂，但是目前还没找到更好的解决办法。当然，通过pl\sql可以逐条处理数据就另当别论了。

总结，在使用层次查询，connect by的时候，如果省略prior xx=xx子句，仅仅指定level的深度，oracle会用所有level=n的节点来作为所有level=n的子节点，产生level=n+1的节点，所以，如何选择这棵树的某条分支，使它上面的每个节点都是一样的（就是根节点和自己连接n-1次，组成一个level为n的树枝，而没有其他分支）？

参考这篇文章，SQL符号分隔的大数据集多行字符串拆分，抛弃了使用层次查询复制行数据的思路。

http://blog.csdn.net/seandba/article/details/72669074