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

Oracle 开发 专栏收录该内容
25 篇文章 0 订阅

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

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

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

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

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



--小数据量+短字符串的情况
select * from t;

ID	NAME
1	a
2	bb
3	ccc

select 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}$');

ID	SPLIT_CHR
1	a
2	b
2	b
3	c
3	c
3	c

以上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 dual
connect 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 dual
connect 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 dual
connect by level <= 1000000;

select id, regexp_substr(x.name, '[^,]+', 1, level) split_chr
  from x
connect by id = prior id
       and prior dbms_random.value is not null
       and level <= regexp_count(name, '[^,]+');


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

  • 1
    点赞
  • 0
    评论
  • 5
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值