专栏首页数据和云巧用SQL:Oracle中实现split相关方法总结

巧用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 然后创建来实现 ,类型拓展或修改比较麻烦,且需要找到相互间的依赖关系再做修改,维护性不太好。当需求发生变化时,也可以简单调整下程序包,就可以方便的实现多列返回等需求。

本文分享自微信公众号 - 数据和云(OraNews),作者:尚世波

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2016-09-02

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 巧用SQL:oracle pl/sql split函数

    李伟 专注于oracle pl/sql开发和Java开发,擅长复杂业务逻辑、算法的pl/sql实现。 背景 在软件开发过程中程序员经常会遇到字符串的...

    数据和云
  • 一篇搞懂MySQL 8.0 Clone技术在线搭建主从复制全过程

    墨墨导读:MySQL从8.0.17开始新增了克隆Clone技术,可以在线进行MySQL的本地克隆或远程克隆,从此搭建从库可以不再需要备份工具来实现了,本文分享C...

    数据和云
  • 一波三折:DBA需要头脑冷清思路清晰解决故障以幸存

    杨廷琨(yangtingkun) 云和恩墨 CTO 高级咨询顾问,Oracle ACE总监,ITPUB Oracle数据库管理版版主 这是一则生产环境的真实维护...

    数据和云
  • Lsof命令详解

    一般root用户才能执行lsof命令,普通用户可以看见/usr/sbin/lsof命令,但是普通用户执行会显示“permission denied”

    一见
  • 如何手动搭建vnpy环境

    请先搭建好运行环境。 编程环境其实就是选一个IDE,Visual Studio或者PyCharm都可以。

    用Python的交易员
  • Monit-开源服务器监控工具 原

    Monit是一个用于管理和监控Unix系统的小型开源工具. Monit进行自动维护和修理, 并且可以在错误情况下执行有意义的因果作用.

    拓荒者
  • 微信 iOS SQLite 源码优化实践

    本文将分享在 SQLite 源码上进行的多线程并发、I/O性能优化等,并介绍优化相关的 SQLite 原理。

    微信终端开发团队
  • 多线程的应用场景

    多线程用于堆积处理,就像一个大土堆,一个推土机很慢,那么10个推土机一起来处理,当然速度就快了,不过由于位置的限制,如果20个推土机,那么推土机之间会产生相互的...

    lyb-geek
  • Java多线程的应用场景和应用目的举例

    多线程用于堆积处理,就像一个大土堆,一个推土机很慢,那么10个推土机一起来处理,当然速度就快了,不过由于位置的限制,如果20个推土机,那么推土机之间会产生相互的...

    Java团长
  • Java多线程的应用场景和应用目的举例

    Spark学习技巧

扫码关注云+社区

领取腾讯云代金券