如何使用SQL ORACLE解析列值

Question

我使用的是ORACLE(版本？(。

我有一个带有列COL_LIST的表mytable，该列包含用"/"分隔的串联值：

COL_LIST
06130/06520
06000/06100/06200/06300

使用SQL，我想创建另一个具有COL_LIST和新列COL的表，例如：

COL    COL_LIST
06130  06130/06520
06520  06130/06520
06000  06000/06100/06200/06300
06100  06000/06100/06200/06300
06200  06000/06100/06200/06300
06300  06000/06100/06200/06300

使用：

SELECT col_list ,
REGEXP_SUBSTR( col_list , '[^/]+' , 1 , LEVEL )  AS col
FROM mytable
CONNECT BY REGEXP_SUBSTR( col_list , '[^/]+' , 1 , LEVEL ) IS NOT NULL

如果我在MYTABLE的COL_LIST中只有一个值(例如06130/06520(，则工作正常，但如果没有，则结果不是我所期望的。

关于这个案子有什么线索吗？

Answer 1

不能这样使用connect by：它会复制行，不会产生您想要的结果。

从您现有的查询开始，假设Oracle12c或更高版本，您可以使用横向连接来解决这个问题：

select t.col_list, x.col
from mytable t
cross apply (
select regexp_substr(t.col_list , '[^/]+', 1, level) as col
from dual
connect by regexp_substr(t.col_list , '[^/]+', 1, level) is not null
) x

Answer 2

如果您不在12c或以上版本，则：

SQL> select regexp_substr(col_list, '[^/]+', 1, column_value) col,
2         col_list
3  from mytable cross join
4    table(cast(multiset(select level
5                        from dual
6                        connect by level <= regexp_count(col_list, '/') + 1
7                       ) as sys.odcinumberlist))
8  order by col_list desc, col;
COL        COL_LIST
---------- -----------------------
06130      06130/06520
06520      06130/06520
06000      06000/06100/06200/06300
06100      06000/06100/06200/06300
06200      06000/06100/06200/06300
06300      06000/06100/06200/06300
6 rows selected.
SQL>

Answer 3

您也可以尝试此解决方案：

SELECT distinct col_list col_lst, REGEXP_SUBSTR( col_list , '[^/]+' , 1 , LEVEL ) col_val,  level lvl
FROM col_single
CONNECT BY level <=  REGEXP_count( col_list , '[^/]+' , level)
order by col_lst desc, lvl;