Oracle SQL符号分隔的多行字符串拆分

之前写了一篇文章，http://blog.csdn.net/seandba/article/details/72644257

讨论了从单行字符串拆分到多行字符串拆分，最后的解决不算圆满。

从一个比较简单的问题出发的，需求如下：

将源表的name列的字符串拆分成一个个字符，不使用union all等语法，因为数据量大了之后扩展性不好。

另外尽量使用一条sql语句，尽量简洁、高效

--小数据量+短字符串的情况select * from t;IDNAME1a2bb3cccselect id, split_chr  from (select t.id,               t.name,               sys_connect_by_path(id, ',') path,               regexp_substr(t.name, '.', 1, level) split_chr          from t         where regexp_substr(t.name, '.', 1, level) is not null        connect by level <= 3) where regexp_like(path, '^(,.)\1{0,2}$');IDSPLIT_CHR1a2b2b3c3c3c以上sql解释请参考上篇文章，http://blog.csdn.net/seandba/article/details/72644257 

虽然解决了上面的简化问题，但是还是存在一个最大的问题，这种写法不能扩展，当记录数稍微增多，字符串长度稍微增长，那就是无尽的等待。上面写法的致命缺陷是没有限制条件（prior xx=yy）的connect by，会导致巨大的连接。忽然间想到，用connect by的用意其实就是复制记录，把一个字符串长度为n的行复制出n行出来，然后结合level来截取即可。一直盯着层次查询、正则表达式，反而忽视了这个最重要的一点。基于以上分析，使用以下写法，扩展性也不错，我使用了100万条记录测试，每条字符串长度为26。基本上无需等待。

具体尝试如下：

--字符串拆分为字符drop table x purge;create table x as select level id, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' name  from dualconnect by level <= 1000000;select id,regexp_substr(x.name, '.', 1, y.lv) split_chr  from x,       (select level lv          from dual        connect by level <=                   (select max(length(name)) level_depth_max from x)) y where length(x.name) >= y.lv;--符号分隔的字符串拆分（以逗号分隔为例） drop table x purge;create table x as select level id, 'A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z' name  from dualconnect by level <= 1000000; select id,regexp_substr(x.name, '[^,]+', 1, y.lv) split_chr  from x,       (select level lv          from dual        connect by level <=                   (select max(regexp_count(name, '[^,]+')) level_depth_max from x)) y --此行下面有说明 where regexp_count(name, '[^,]+') >= y.lv;说明：这一行的查询做了一次全表扫描，目的是获取需要拆分列的实际最大长度，如果全表扫描代价太大，可以尝试直接赋值为本列的宽度。 

--在另外一个地方看到这么一个写法，很有趣，使用(prior dbms_random.value is not null)消除了循环，也就是把我们上面讨论的(小数据量+短字符串的情况)之所以不能扩展的巨大的连接给消除了，所以也能支持大数据量和长字符串的拆分了--网址：http://www.itpub.net/thread-1803498-1-1.html 见网友hh7yx的回复--有兴趣可以比较下两种写法的执行计划和效率drop table x purge;create table x as select level id, 'A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z' name  from dualconnect by level <= 1000000;select id, regexp_substr(x.name, '[^,]+', 1, level) split_chr  from xconnect by id = prior id       and prior dbms_random.value is not null       and level <= regexp_count(name, '[^,]+');

总结，虽然可以通过pl/sql大而化小，或者通过编写函数处理，但是通过系统自带的函数和语法解决问题看起来还是非常简洁的。