Oracle中REGEXP_SUBSTR函数的使用(拆分字符串动态生成行)

在oracle中，使用一条语句实现将’10,20,30’拆分成’10’，‘20’，'30’的数据列。

REGEXP_SUBSTR函数格式如下：

function REGEXP_SUBSTR(String, Pattern, Position, Occurrence, Modifier)

_String ：需要进行正则处理的字符串

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

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

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

_Modifier ：模式（'i’不区分大小写进行检索；‘c’区分大小写进行检索。默认为’c’。）

1. 查询使用正则分割后的第一个值10

SELECT REGEXP_SUBSTR('10,20,30','[^,]+',1,1,'i') AS TEST 
FROM DUAL;

结果集如下：

2. 查询使用正则分割后的最后一个值30

SELECT REGEXP_SUBSTR('10,20,30','[^,]+',1,3,'i') AS TEST 
FROM DUAL;

结果集如下：

3. 实现动态序列（CONNECT BY LEVEL的基本使用）

SELECT LEVEL 
FROM DUAL CONNECT BY LEVEL <=10;

结果集如下：

4. 将 REGEXP_SUBSTR 与 CONNECT BY LEVEL 关联起来

SELECT NVL(REGEXP_SUBSTR('10,20,30', '[^,]+', 1, LEVEL, 'i'), '空') AS TEST 
FROM DUAL CONNECT BY LEVEL <= 10;

结果集如下：

5. 优化后的SQL语句，让生成的行数量符合实际情况，不会有为空的行。

SELECT REGEXP_SUBSTR('10,20,30', '[^,]+', 1, LEVEL, 'i') AS TEST 
FROM DUAL 
CONNECT BY LEVEL <= LENGTH('10,20,30') - LENGTH(REGEXP_REPLACE('10,20,30', ',', ''))+1;

结果集如下：