Oracle以逗号分隔的字符串拆分为多行数据实例详解

作者:南清风 时间:2024-01-13 16:32:46 

前言

近期在工作中遇到某表某字段是可扩展数据内容,信息以逗号分隔生成的,现需求要根据此字段数据在其它表查询相关的内容展现出来,第一想法是切割数据,以逗号作为切割符,以下为总结的实现方法,以供大家参考、指教。

Oracle以逗号分隔的字符串拆分为多行数据实例详解

1、regexp_substr函数,通过正则来拆分字符串,函数用法为:(必须是oracle 10g+的版本才支持)

 REGEXP_SUBSTR函数格式如下:

function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)

__srcstr :需要进行正则处理的字符串

__pattern :进行匹配的正则表达式

__position :起始位置,从第几个字符开始正则表达式匹配(默认为1)

__occurrence :获取第几个分割出来的组(分割后最初的字符串会按分割的顺序排列成组),默认为1

__modifier :模式('i'不区分大小写进行检索;'c'区分大小写进行检索。默认为'c'。)针对的是正则表达式里字符大小写的匹配

-------------------------------------------------------------------------------------------------------------------

Oracle以逗号分隔的字符串拆分为多行数据实例详解

  此函数只能每次取一个字符串出来,有点鸡肋,字符串中逗号的数量是不确定的,如果有2个逗号,需要提取的字段就是3个。为了确定有多少个需要提取的字段,需要用到connect by命令实现动态参数构造连续的值,通过原字符串长度和被替换后字符串长度相减,可以得到原字符串中的逗号数量,加1后得到需要提取的匹配字段数量。

Oracle以逗号分隔的字符串拆分为多行数据实例详解

 SQL:


select bs from cs1_0 where slid='201804100038'
--正则分割后的第一个值
SELECT REGEXP_SUBSTR((select bs from cs1_0 where slid='201804100038'),'[^,]+',1,1,'i') as 分割后结果 FROM DUAL;
--获取一个多个数值的列,从而能够让结果以多行的形式展示出来
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=5;
--将上面REGEXP_SUBSTR的occurrence(标识第几个匹配组)实现动态参数,使用 connect by组合起来
SELECT REGEXP_SUBSTR((select bs from cs1_0 where slid='201804100038'),'[^,]+',1,LEVEL,'i') as 分割后结果 FROM DUAL CONNECT BY LEVEL <=5;
--优化一下(动态获匹配组标识行数)
select regexp_substr((select bs from cs1_0 where slid='201804100038'),'[^,]+',1,LEVEL,'i') as 分割后结果 from dual
connect by level <= length((select bs from cs1_0 where slid='201804100038'))-length(regexp_replace((select bs from cs1_0 where slid='201804100038'),',',''))+1;

2、以Type类型和function函数的方式实现


1)建立TYPE类型

CREATE OR REPLACE TYPE strsplit_type_12 IS TABLE OF VARCHAR2 (4000)

2)建立function存储函数
create or replace function strsplit_66(p_value varchar2,p_split varchar2 )  --字符串,切割符
--根据特定字符来切割字符串
return strsplit_type_12
 pipelined is
 v_idx       integer;
 v_str       varchar2(500);
 v_strs_last varchar2(4000) := p_value;

begin
 loop
   v_idx := instr(v_strs_last, p_split);
   exit when v_idx = 0;
   v_str       := substr(v_strs_last, 1, v_idx - 1);
   v_strs_last := substr(v_strs_last, v_idx + 1);
   pipe row(v_str);
 end loop;
 pipe row(v_strs_last);
 return;
end strsplit_66;

SELECT ROWNUM 序号, a.* FROM TABLE(strsplit_66((select bs from cs1_0 where slid='201804100038'), ',')) a;

 测试一下:

Oracle以逗号分隔的字符串拆分为多行数据实例详解

总结

来源:https://www.cnblogs.com/su-root/p/15008584.html

标签:oracle,拆分,多行
0
投稿

猜你喜欢

  • python网络应用开发知识点浅析

    2021-07-15 00:09:48
  • hive中将string数据转为bigint的操作

    2024-01-20 15:37:18
  • Django 项目布局方法(值得推荐)

    2022-08-22 12:44:22
  • SqlServer 2005 简单的全文检索

    2024-01-18 05:00:31
  • django重新生成数据库中的某张表方法

    2024-01-23 01:14:45
  • CSS教程:简单理解em

    2008-07-03 12:44:00
  • 浅析python中的分片与截断序列

    2022-05-11 02:31:03
  • php常见的页面跳转方法汇总

    2024-05-28 15:38:15
  • 利用Python3分析sitemap.xml并抓取导出全站链接详解

    2022-01-26 16:27:08
  • 基于JavaScript中标识符的命名规则介绍

    2024-04-19 10:48:01
  • 配置SQL Server文件组对应不同的RAID系统

    2009-01-04 14:31:00
  • ASP网站程序自动升级实现的方法

    2008-10-15 14:28:00
  • sql在一个表中添加字段并添加备注的方法

    2024-01-26 10:16:59
  • Python中的上下文管理器和with语句的使用

    2021-01-28 00:29:51
  • MySQL UPDATE时主键冲突引发的思考

    2008-06-21 17:00:00
  • Python调用系统命令os.system()和os.popen()的实现

    2021-06-27 23:56:29
  • sql语句中单引号,双引号的处理方法

    2024-01-20 06:59:00
  • 关于JSON以及JSON在PHP中的应用技巧

    2023-11-16 00:03:38
  • Python数据结构之顺序表的实现代码示例

    2021-02-22 07:13:08
  • Python利用Pydub实现自动分割音频

    2022-10-08 22:02:48
  • asp之家 网络编程 m.aspxhome.com