Oracle分割字段的值并且返回多行数据（使用regexp_substr和regexp_count函数）

1.需求

需要将数据库的表中某个字段的值，根据逗号来分割后，返回多行数据。
2.实现过程
2.1REGEXP_SUBSTR()

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

    string:需要进行正则处理的字符串
    pattern：进行匹配的正则表达式
    position：起始位置，从字符串的第几个字符开始正则表达式匹配（默认为1） 注意：字符串最初的位置是1而不是0
    occurrence：获取第几个分割出来的组（分割后最初的字符串会按分割的顺序排列成组）
    modifier：模式（‘i’不区分大小写进行检索；‘c’区分大小写进行检索。默认为’c’）针对的是正则表达式里字符大小写的匹配

使用REGEXP_SUBSTR()进行逗号的匹配、字符串的分割和获取。

举个例子，这是取字符串中根据逗号分割后的第一组数据

SELECT REGEXP_SUBSTR('5FB146519670485C95E6A5C11E389CD2,8421733E4F7C4DFC8978A3BD54D2D66A', '[^,]+', 1, 1) FROM dual;

    1

结果：
在这里插入图片描述
那我们现在将occurrence值动态获取并赋值给REGEXP_SUBSTR函数即可。

可以发现分割后的字符串个数= 分割符号个数 + 1

接下来获取分割符号的个数即可。
有两种方法第一种是把字符串的分割符号替换成’’，然后使用length函数计算原来字符串的长度减去替换后的字符串长度得到分割符号的个数，再加一即可。

select LENGTH('5FB146519670485C95E6A5C11E389CD2,8421733E4F7C4DFC8978A3BD54D2D66A') - LENGTH(REGEXP_REPLACE('5FB146519670485C95E6A5C11E389CD2,8421733E4F7C4DFC8978A3BD54D2D66A', ',', '')) + 1 from dual;

    1

结果：
在这里插入图片描述
第二种使用regexp_count函数获取分割符号的个数。
2.2regexp_count()

Oracle的11g引入此函数
REGEXP_COUNT ( source_char, pattern [, position [, match_param]])

    source_char:需要进行正则处理的字符串
    pattern ：进行匹配的正则表达式
    position：起始位置，从字符串的第几个字符开始正则表达式匹配（默认为1） 注意：字符串最初的位置是1而不是0
    match_param：‘i’ 用于不区分大小写的匹配，‘c’ 用于区分大小写的匹配，‘n’ 允许句点(.)作为通配符去匹配换行符。如果省略该参数，则句点将不匹配换行符，‘m’ 将源串视为多行。即Oracle 将^和$分别看作源串中任意位置任何行的开始和结束，而不是仅仅看作整个源串的开始或结束。如果省略该参数，则Oracle将源串看作一行。‘x’ 忽略空格字符。默认情况下，空格字符与自身相匹配。

REGEXP_COUNT 返回pattern 在source_char 串中出现的次数。如果未找到匹配，则函数返回0。position 变量告诉Oracle 在源串的什么位置开始搜索。在开始位置之后每出现一次模式，都会使计数结果增加1。

举个例子

SELECT regexp_count('5FB146519670485C95E6A5C11E389CD2,8421733E4F7C4DFC8978A3BD54D2D66A', ',')+1 FROM dual;

    1

结果：
在这里插入图片描述
2.3组装sql

思路：使用rownum关键字标记occurrence的值，因为rownum算是伪列，所以使用rownum小于分割符号的个数来取分割后的两个字符串。

SELECT REGEXP_SUBSTR('5FB146519670485C95E6A5C11E389CD2,8421733E4F7C4DFC8978A3BD54D2D66A', '[^,]+', 1, ROWNUM)
FROM dual
CONNECT BY ROWNUM <= regexp_count('5FB146519670485C95E6A5C11E389CD2,8421733E4F7C4DFC8978A3BD54D2D66A', ',')+1;

    1
    2
    3

结果
3.实际环境遇到的问题
3.1分割后的字段不可用于关联

使用分割后的字段，尽管取了别名，但是用与关联其他表的关联字段时，会出现报错。
报错信息：ORA-00932:数据类型不一致:应为-,但却获得CLOB