在 Oracle 中拆分包含逗号分隔值的两列

Question

我在一个表格中有两列，用逗号分隔的值，它是如何将其分成行的？

Answer 1

这会有帮助吗？

SQL> with test (col1, col2) as
2    (select 'Little,Foot,is,stupid', 'poor,bastard'         from dual union all
3     select 'Green,mile,is,a'      , 'good,film,is,it,not?' from dual
4    )
5  select regexp_substr(col1 ||','|| col2, '[^,]+', 1, column_value) str
6  from test cross join
7       table(cast(multiset(select level from dual
8                           connect by level <= regexp_count(col1 ||','|| col2, ',') + 1
9                          ) as sys.odcinumberlist));
STR
--------------------------------------------------------------------------------
Little
Foot
is
stupid
poor
bastard
Green
mile
is
a
good
film
is
it
not?
15 rows selected.
SQL>

Answer 2

使用递归子查询分解子句和简单的字符串函数：

WITH splits ( id, c1, c2, idx, start_c1, end_c1, start_c2, end_c2 ) AS (
SELECT id,
c1,
c2,
1,
1,
INSTR( c1, ',', 1 ),
1,
INSTR( c2, ',', 1 )
FROM   test_data
UNION ALL
SELECT id,
c1,
c2,
idx + 1,
CASE end_c1 WHEN 0 THEN NULL ELSE end_c1 + 1 END,
CASE end_c1 WHEN 0 THEN NULL ELSE INSTR( c1, ',', end_c1 + 1 ) END,
CASE end_c2 WHEN 0 THEN NULL ELSE end_c2 + 1 END,
CASE end_c2 WHEN 0 THEN NULL ELSE INSTR( c2, ',', end_c2 + 1 ) END
FROM   splits
WHERE  end_c1 > 0
OR     end_c2 > 0
)
SELECT id,
idx,
CASE end_c1
WHEN 0
THEN SUBSTR( c1, start_c1 )
ELSE SUBSTR( c1, start_c1, end_c1 - start_c1 )
END AS c1,
CASE end_c2
WHEN 0
THEN SUBSTR( c2, start_c2 )
ELSE SUBSTR( c2, start_c2, end_c2 - start_c2 )
END AS c2
FROM   splits s
ORDER BY id, idx;

所以对于测试数据：

CREATE TABLE test_data ( id, c1, c2 ) AS
SELECT 1, 'a,b,c,d', 'e,f,g' FROM DUAL UNION ALL
SELECT 2, 'h', 'i' FROM DUAL UNION ALL
SELECT 3, NULL, 'j,k,l,m,n' FROM DUAL;

这输出：

身份证 |IDX |C1 |C2  -: |--: |:--- |:---  1 |  1 |一 |e    1 |  2 |乙 |f    1 |  3 |c |g    1 |  4 |D |空2 |  1 |h |我    3 |  1 |空|j    3 |  2 |空|k    3 |  3 |空|l    3 |  4 |空|m    3 |  5 |空|n

db<>小提琴在这里