oracle逗号分割列并转成多行

使用场景介绍：

业务表A中一个字段存放用逗号分割的多个业务单元，现在需要将数据转成一个业务单元对应一个数据。然后关联出业务单元表B，获取更加详细的业务单元信息。

1、业务表A

2、业务单元表B

3、预期结果

1、按逗号分割后结果

2、与业务单元表 B关联结果

4、实现的sql

1、按逗号分割

-- 写法一
SELECT
	A.*,
	REGEXP_SUBSTR( A.L2UNIT_NAME, '[^,]+', 1, L ) AS L2UNIT_NAME_B,
	L 
FROM
	JYZB_DATA_PERM A,
	( SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 100 ) 
WHERE
	L ( + ) <= LENGTH( A.L2UNIT_NAME ) - LENGTH( REPLACE ( A.L2UNIT_NAME, ',' ) ) + 1 
ORDER BY
	id
	
	
-- 写法二 
SELECT
	A.*,
	REGEXP_SUBSTR( A.L2UNIT_NAME, '[^,]+', 1, L ) AS L2UNIT_NAME_B,
	L 
FROM
	JYZB_DATA_PERM A,
	( SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 100 ) --限制一下level数量，提高查询速度。否则数据较多的情况下，会一直查询不出结果
WHERE
	L ( + ) <= regexp_count ( A.L2UNIT_NAME, ',' ) + 1 -- 计算需要分割后的数量
ORDER BY
	id

2、关联查询

SELECT
	jj.*,
	ij.* 
FROM
	(
	SELECT
		cp.*,
		regexp_count ( CP.L2UNIT_NAME, ',' ) + 1 L2UNIT_NAME_count,
		REGEXP_SUBSTR( CP.L2UNIT_NAME, '[^,]+', 1, L ) AS L2UNIT_NAME_B,
		L 
	FROM
		JYZB_DATA_PERM CP,
		( SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 100 ) 
	WHERE
		L ( + ) <= LENGTH( CP.L2UNIT_NAME ) - LENGTH( REPLACE ( CP.L2UNIT_NAME, ',' ) ) + 1 
	) jj
	LEFT JOIN invalid_JYZB_BASE_L2UNIT ij ON jj.L2UNIT_NAME_B = ij.L2UNIT_NAME 
ORDER BY
	id

5、sql分析

5.1 函数 REGEXP_SUBSTR

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

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

5.2 案列分析

5.2.1 案例1

SELECT REGEXP_SUBSTR('11a22A33a','[^A]+',1,1,'i') AS STR FROM DUAL;

说明：

结果： 11
分析： 正则表达式是以A为标识进行分割，而'i'标识不区分大小写，所以结果是11，而不是11a22

5.2.1 案例2

SELECT REGEXP_SUBSTR('11a22A33a','[^A]+',1,1,'c') AS STR FROM DUAL;

说明：

结果： 11a22
分析： 正则表达式是以A为标识进行分割，而'c'标识区分大小写，所以结果是11a22，而不是11

5.3 关联REGEXP_SUBSTR函数

SELECT REGEXP_SUBSTR('11a22A33a','[^A]+',1,LEVEL,'c') AS STR FROM DUAL CONNECT BY LEVEL<=5;

结果：

分析： 把要输出来的第几个子串，通过一个变量level转换成输出多少个子串。level<=5代表的是输出5个，没有的为null。这样显然会输出多余null值，这不是我们想要的。
优化：

SELECT
	REGEXP_SUBSTR( '11a22A33a', '[^A]+', 1, LEVEL, 'c' ) AS STR
FROM
	DUAL CONNECT BY LEVEL <= length( '11a22A33a' ) - length( REGEXP_REPLACE( '11a22A33a','A','' ) ) + 1;

或者：

SELECT
	REGEXP_SUBSTR( '11a22A33a', '[^A]+', 1, LEVEL, 'c' ) AS STR 
FROM
	DUAL CONNECT BY LEVEL <= ( regexp_count ( '11a22A33a', 'A' ) + 1 )

结果

level扩展

Length函数

返回字符的个数

Select length('你好') from dual;  2

REGEXP_REPLACE 函数

-- 在字符串中搜索正则表达式模式并将该模式的每个匹配项替换为指定字符串
VARCHAR REGEXP_REPLACE(VARCHAR str, VARCHAR pattern, VARCHAR replacement)

入参

示例

Select REGEXP_REPLACE( '11a22A33a','A','' ) from dual;  

结果 — 11a2233a

REGEXP_COUNT函数

REGEXP_COUNT用于为正则表达式搜索字符串，且返回正则表达式出现次数

INTEGER REGEXP_COUNT
(
  srcstr    TEXT,
  pattern   TEXT,
  position  DEFAULT 1
  modifier  DEFAULT NULL
)      

参数

示例

-- regexp_count函数计算指定字符串在字符串中出现的个数
SELECT
	regexp_count ( '11a22A33a', 'A' ) AS count 
FROM
	dual;
-- 结果 1

6、注意

如果粗暴的使用逗号分割数据量比较大的情况下可能会出现查询时间很长。

SELECT
	A.*,
	REGEXP_SUBSTR( A.L2UNIT_NAME, '[^,]+', 1, LEVEL ) AS L2UNIT_NAME_B 
FROM
	JYZB_DATA_PERM A CONNECT BY LEVEL <= regexp_count ( A.L2UNIT_NAME, ',' ) + 1

在实际使用时，使用了此方式导致sql一直在执行，查询不出结果。
正确方式

SELECT
	A.*,
	REGEXP_SUBSTR( A.L2UNIT_NAME, '[^,]+', 1, L ) AS L2UNIT_NAME_B,
	L 
FROM
	JYZB_DATA_PERM A,
	( SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 100 ) --限制一下level数量，提高查询速度。否则数据较多的情况下，会一直查询不出结果
WHERE
	L ( + ) <= regexp_count ( A.L2UNIT_NAME, ',' ) + 1  -- 计算需要分割后的数量
ORDER BY
	id

扩展连接

1、Oracle数据库中某个表中的某个字段的值是用逗号隔开的多个值，根据逗号拆分并从关联表中查出数据返回
2、mysql数据库中某个表中的某个字段的值是用逗号隔开的多个值，根据逗号拆分并从另一个表中查出数据返回