首先简单介绍下 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