达梦数据库(oracle)使用 REGEXP_SUBSTR函数实现根据逗号将一行数据拆分成多行数据

最新推荐文章于 2024-10-24 11:34:25 发布

原创最新推荐文章于 2024-10-24 11:34:25 发布 · 1.2w 阅读

CC 4.0 BY-SA版权

文章标签：

首先简单介绍下 REGEXP_SUBSTR函数,函数格式如下：
function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)
__srcstr ：需要进行正则处理的字符串

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

__position ：起始位置，从第几个字符开始正则表达式匹配（默认为1）

__occurrence ：标识第几个匹配组，默认为1

__modifier ：模式（'i’不区分大小写进行检索；‘c’区分大小写进行检索。默认为’c’。可以省略）
另一般REGEXP_SUBSTR函数都会与CONNECT BY配合使用

业务场景

现有新闻图片库NEWS_TYPE表,字段如下
在这里插入图片描述
如图所示,每一条数据都对应着一个或多个ATTACH_ID,这样如果我们想要与ATTACH表进行联合查询就会很麻烦,使用REGEXP_SUBSTR能够解决这个问题.

SELECT DISTINCT 
ID, REGEXP_SUBSTR(ATTACH_ID, '[^,]+', 1, LEVEL) AS FILE_ONE
FROM NEWS_TYPE
CONNECT BY LEVEL <= LENGTH(ATTACH_ID) - LENGTH(regexp_replace(ATTACH_ID, ',', '')) + 1 
ORDER BY ID

查询结果如下:
在这里插入图片描述

好了下面问题来了,如果数据量小的话,这样来操作是没问题的,一旦数据量上来了,并且再加上多表联查,查询的速度就会非常慢(非常非常慢!),所以就有了下面的内容,优化.
经过多方查询资料,新的sql查询速度确实快到飞起,但实现原理一直都不清楚,等我查到以后再更新,先上新sql

SELECT ID, 
REGEXP_SUBSTR(ATTACH_ID, '[^,]+', 1, l) AS FILE_ONE
FROM NEWS_TYPE,(SELECT LEVEL l FROM DUAL CONNECT BY LEVEL<=100) b
WHERE l <= LENGTH(ATTACH_ID) - LENGTH(regexp_replace(ATTACH_ID, ',', '')) + 1