Oracle 字符串转多行(REGEXP_SUBSTR)

方案一：

SQL

1.一个数据表(TABLE1_ZK)中存在一个字段(STRS)（存储格式是以【,】隔开的字符串）

2.现需要将其查分为多行数据（每行为其中一个字符串）

3.sql

SELECT t.id,
           REGEXP_SUBSTR(t.STRS, '[^,]+', 1, LEVEL) AS mat  
      FROM (
           select bds.id,bds.STRS 
           from  TABLE1_ZK bds 
           where bds.STRS is not null 
       ) t
    CONNECT BY LEVEL <=  regexp_count(t.STRS, ',')+1 
    and t.id = prior t.id  and prior dbms_random.value is not null

4.查询结果

Oracle 字符串转多行(REGEXP_SUBSTR)

可能遇见的问题：

SpringBoot druid 可能会报SQL注入失败：java.sql.SQLException: sql injection violation, deny object : dbms_random

1.原因：

yml中进行了wall（防火墙）配置；wall拦截了dbms_random

Oracle 字符串转多行(REGEXP_SUBSTR)

2.解决：

去掉其中wall配置即可

方案二：

若业务系统不允许去除wall

更换SQL：

select distinct bds.id, regexp_substr(bds.STRS, '[^,]+', 1, Level,'i') str
from TABLE1_ZK bds 
where bds.STRS is not null 
connect by Level <= LENGTH(bds.STRS) - LENGTH(REGEXP_REPLACE(bds.STRS, ',', '')) + 1

查询结果：

Oracle 字符串转多行(REGEXP_SUBSTR)

注：大数据量(多行且需字段转行数量较大)可能会出现死循环；

建议程序中循环处理，查询单条并转换；

Oracle 字符串转多行(REGEXP_SUBSTR)

相关推荐