0

巧用SQL:Oracle中实现split相关方法总结

尚世波 2016-09-02
182


尚世波

从事数据库方面工作多年,专注于pl/sql开发、数据库设计、优化方面的研究,喜欢挑战


编辑寄语:关于上次发布的 Oracle split 函数的实现,有网友积极参与互动,提出更多的方法和建议,我们拣选了尚世波先生的反馈文章分享给大家!希望大家多多交流,参与互动,感谢支持!


前文回顾:巧用SQL:oracle pl/sql split函数


看完上次的分享, 我很有感触,在软件开发过程中经常会出现按照某个字符进行分割字符串的情形,在网上也有很多这样的方法,我收集了下并对他们做了下汇总和验证。文章以‘,’(英文逗号)分割为例,另外设想传入的字符串为未知变量,书写通用的sql进行说明和演示


方法一:sql实现方法之正则表达式

可以使用 Oracle 自带的正则函数 regexp_count 和 regexp_substr 来实现,这种方法很简单,并且考虑了字符串以分隔符结尾的情况。但要注意的是,该方法中使用的正则函数 regexp_substr 在10g及以后的版本中执行都是比较顺利的,而regexp_count 函数则需要在11g及以后的版本中才能执行。因此这种方法适用于11g及以上版本的数据库。

select regexp_substr('1,25,3,ftet775##,8,6,9', '[^,]+',1,rownum)

  from dual connectbyrownum<=regexp_count('1,25,3,ftet775##,8,6,9','[,]')-

 regexp_count('1,25,3,ftet775##,8,6,9','(,$)')+1;


结果如下:



方法二:SQL实现方法之一般函数写法

相比较第一种正则函数的方法,普通的sql函数比较通用化,各个版本都支持。但写法稍复杂

select substr(inlst,

                instr(inlst, ',', 1, rownum) +1,

                instr(inlst, ',', 1, rownum +1) -

                instr(inlst, ',', 1, rownum) -1)

   from (select ',' || '1,25,3,ftet775##,8,6,9,' || ',' inlst from dual)

 connect by rownum <= length(inlst) - length(replace(inlst, ',', ''))- 1 -

            decode(substr(inlst, -2, 1), ',', 1, 0);


结果如下

注:为了简化初始判断我在字符串前和末尾分别加了分割字符,在未知的情况下,我们使用这两个函数,需要判断下末尾是否有分隔符。不然很可能对结果产生影响。

(1)一般情况下,输入n个字符串,加入n-1个分隔符,即末尾没有分隔符的时候,判断或者不判断不会影响结果。


如下图:



(2)当输入字符的末尾有分隔符的时候,判断与不判断的结果不同

a.不判断末尾分隔符



(2)判断末尾分隔符



方法三:PL/SQL实现方法之管道函数

使用管道函数也可以很方便的实现,调用方便,但是代码量较多

实现脚本如下:

① 创建基础类型包

  SQL> create or replace package base_type_library_pkg is

  type ba_type is record(col_membervarchar2(200)) ;

  type ba_tab_type is table of ba_type;

end base_type_library_pkg;

/

Package created


② 创建函数实现包

create orreplace package get_split_table_pkg is 

  -- Author : Jason Shang

  -- Created : 2016/8/12 14:07:09

  -- Purpose :

function fn_get_split_table(i_in_char clob,i_split varchar2)

    return base_type_library_pkg.ba_tab_type

    pipelined;

endget_split_table_pkg;

/

create orreplace package body get_split_table_pkg is

  function fn_get_split_table(i_in_char clob,i_split varchar2)

    return base_type_library_pkg.ba_tab_type

    pipelined is

    v_ty_member base_type_library_pkg.ba_type;

    v_count    number := 0;

    v_flag     number := 0;

    v_len      number ;

  begin

    if i_in_char is null or i_split is nullthen

      return;

    end if;

    v_count := length(i_in_char) -length(replace(i_in_char, i_split, ''));

    v_len :=length(i_split);

    if substr(i_in_char, -1*v_len) = i_splitthen

      v_flag := 1;

    end if;

    v_count := v_count/v_len + 1 - v_flag;

    for i in 1 .. v_count loop

      v_ty_member.col_member := substr(i_split|| i_in_char || i_split,

                                      instr(i_split || i_in_char || i_split,

                                             i_split,

                                             1,

                                             i)+ v_len,

                                      instr(i_split || i_in_char || i_split,

                                             i_split,

                                             1,

                                             i+ 1) -

                                      instr(i_split || i_in_char || i_split,

                                            i_split,

                                             1,

                                             i)- v_len);

      pipe row(v_ty_member);

    end loop; 

  end fn_get_split_table;

endget_split_table_pkg;

/


 测试结果如下图

(1)单分隔符



(2)多分隔符:


之所以选择包中创建函数和基础类型的方式来实现,主要是考虑到后续拓展和后续性能优化、调整时,可以比较方便的实现修改,代码的可读性也相对较好。因为如果直接创建成类型,后续修改时,需要层层 drop 然后创建来实现 ,类型拓展或修改比较麻烦,且需要找到相互间的依赖关系再做修改,维护性不太好。当需求发生变化时,也可以简单调整下程序包,就可以方便的实现多列返回等需求。


如何加入"云和恩墨大讲堂"微信群

搜索 盖国强(Eygle) :eyygle,或者扫描下面二维码,备注:云和恩墨大讲堂,即可入群。每周与千人共享免费技术分享,与讲师在线讨论。



近期文章

分区剪裁特性剖析

oracle标量子查询和表连接改写

利用DMU修改数据库字符集 

UPDATE GLOBAL_NAME为空之后的恢复

深入剖析 ORA-04031 的前世今生

Database Link与GLOBAL_NAMES参数的关系


资源下载

(OraNews)回复关键字获取

2016DTCC, 2016数据库大会PPT;

DBALife,"DBA的一天"精品海报大图;

12cArch,“Oracle 12c体系结构”精品海报;

DBA01,《Oracle DBA手记》第一本下载;

YunHe“云和恩墨大讲堂”案例文档下载;


最后修改时间:2020-05-08 00:20:48
「喜欢文章,快来给作者赞赏墨值吧」
文章转载自尚世波,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论