Oracle REGEXP_SUBSTR（）函数（列转行\行转列）

原创已于 2022-10-13 14:08:42 修改 · 1.1k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库 #sql

于 2022-10-13 14:07:25 首次发布

oracle 专栏收录该内容

2 篇文章

订阅专栏

本文介绍在数据库操作中如何实现列转行与行转列的技术，包括使用REGEXP_REPLACE进行字符替换、利用REGEXP_SUBSTR进行字符串切割转换及通过wm_concat聚合函数完成行数据到列数据的转变。

1、开发过程经常遇到“列转行\行转列”问题，如下图

2、列转行

2.1 现场数据库一般会有中文逗号，分号、、、、、等情况。转之前我们先进行替换
select 
  (SELECT REGEXP_REPLACE(m.ALTBE,'[；||;||，||、]', ',') AS NAMES FROM DUAL) 
from REG_AltAccItem m  WHERE  id = '141000013302990181'

2.2 列转行
SELECT REGEXP_SUBSTR('100000003,100000007,100000018,100000020,100000021,100000022,100000023,410000002,410000003,','[^,]+', 1, ROWNUM) AS code FROM  DUAL 
	CONNECT BY ROWNUM <= LENGTH(REGEXP_REPLACE('100000003,100000007,100000018,100000020,100000021,100000022,100000023,410000002,410000003,', '[^,]+', NULL)) + 1

3、行转列

参数解释
String:需要进行正则处理的字符串。
pattern:正则表达式。
position:起始位置（从字符串的第几个开始正则，默认为1，注：数据库中的字符串起始位置为1）。
occurrence:获取第几组通过正则表达式分割出来的组。
modifier:模式（‘i’不区分大小写，‘c’区分大小写。其中默认没‘c’）。

SELECT wm_concat(NAME) FROM (
	select NAME from CODE_REGALLLICCODE a INNER JOIN (
	SELECT REGEXP_SUBSTR('100000003,100000007,100000018,100000020,100000021,100000022,100000023,410000002,410000003,','[^,]+', 1, ROWNUM) AS code FROM  DUAL 
	CONNECT BY ROWNUM <= LENGTH(REGEXP_REPLACE('100000003,100000007,100000018,100000020,100000021,100000022,100000023,410000002,410000003,', '[^,]+', NULL)) + 1)  b ON a.CODE = b.code
)