巧用SQL：Oracle中实现split相关方法总结
尚世波
从事数据库方面工作多年，专注于pl/sql开发、数据库设计、优化方面的研究，喜欢挑战
前文回顾：巧用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 然后创建来实现 ，类型拓展或修改比较麻烦，且需要找到相互间的依赖关系再做修改，维护性不太好。当需求发生变化时，也可以简单调整下程序包，就可以方便的实现多列返回等需求。