当前位置：首页 > 数据库 > oracle > 详细页面

Oracle一行拆分为多行方法实例

时间：2023-10-31来源：系统城装机大师作者：佚名

一、背景

拼接字符串根据某种规律拆分并转化为多行，只要拆分的主键和数据即可

二、预期结果

1、表A

2、去除无效的字符，如'['、']'等

3、按逗号分割后结果

三、实现sql

1、去除无效的字符，如'['、']'等

1
2
3
4
5 SELECT ID,
replace(replace(replace(test /*替换的列*/, '[', ''), ']', ''),
' ',
'')
FROM table /*替换的表*/

2、按逗号分割后结果

法一：

1
2
3
4
5
6
7
8
9
10 SELECT distinct ID,
REGEXP_SUBSTR(test1 /*替换拆分的列*/,
'[^,]+',
1,
LEVEL,
'i') as test2
FROM table /*替换表*/
CONNECT BY LEVEL <= LENGTH(test1 /*替换拆分的列*/) -
LENGTH(REPLACE(test1 /*替换拆分的列*/, ',', '')) + 1
order by ID

法二：

1
2
3
4
5
6
7
8
9 SELECT distinct ID,
REGEXP_SUBSTR(test1 /*替换拆分的列*/,
'[^,]+',
1,
LEVEL,
'i') as test2
FROM table /*替换表*/
CONNECT BY LEVEL <= (regexp_count(test1 /*替换拆分的列*/, ',') + 1)
order by ID

法三：数据量大时优先选用，效率高

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 SELECT ID,
substr(test1 /*替换拆分的列*/,
instr(test1 /*替换拆分的列*/, ',', 1, levels.lvl) + 1,
instr(test1 /*替换拆分的列*/, ',', 1, levels.lvl + 1) -
(instr(test1 /*替换拆分的列*/, ',', 1, levels.lvl) + 1)) as test1 /*替换拆分的列*/
FROM (SELECT id,
',' || test1 /*替换拆分的列*/
|| ',' AS test1 /*替换拆分的列*/,
length(test1 /*替换拆分的列*/) -
nvl(length(REPLACE(test1 /*替换拆分的列*/, ',')), 0) + 1 AS cnt
FROM table /*替换表*/
) a,
(SELECT rownum AS lvl
FROM (SELECT MAX(length(test1 /*替换拆分的列*/
|| ',') -
nvl(length(REPLACE(test1 /*替换拆分的列*/, ',')),
0)) max_len
FROM table /*替换表*/
)
CONNECT BY LEVEL <= max_len) levels
WHERE levels.lvl <= a.cnt
order by ID