本文介绍了通过 Oracle SQL 查询将行中的列的逗号分隔值拆分的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!
问题描述
我有一张如下表:
-------------
ID | NAME
-------------
1001 | A,B,C
1002 | D,E,F
1003 | C,E,G
-------------
我希望这些值显示为:
-------------
ID | NAME
-------------
1001 | A
1001 | B
1001 | C
1002 | D
1002 | E
1002 | F
1003 | C
1003 | E
1003 | G
-------------
我尝试过:
select split('A,B,C,D,E,F', ',') from dual; -- WILL RETURN COLLECTION
select column_value
from table (select split('A,B,C,D,E,F', ',') from dual); -- RETURN COLUMN_VALUE
推荐答案
尝试使用以下查询:
WITH T AS (SELECT 'A,B,C,D,E,F' STR FROM DUAL) SELECT
REGEXP_SUBSTR (STR, '[^,]+', 1, LEVEL) SPLIT_VALUES FROM T
CONNECT BY LEVEL <= (SELECT LENGTH (REPLACE (STR, ',', NULL)) FROM T)
下面带有 ID 的查询:
Below Query with ID:
WITH TAB AS
(SELECT '1001' ID, 'A,B,C,D,E,F' STR FROM DUAL
)
SELECT ID,
REGEXP_SUBSTR (STR, '[^,]+', 1, LEVEL) SPLIT_VALUES FROM TAB
CONNECT BY LEVEL <= (SELECT LENGTH (REPLACE (STR, ',', NULL)) FROM TAB);
尝试对多个 ID 和多个分隔使用以下查询:
Try using below query for multiple IDs and multiple separation:
WITH TAB AS
(SELECT '1001' ID, 'A,B,C,D,E,F' STR FROM DUAL
UNION
SELECT '1002' ID, 'D,E,F' STR FROM DUAL
UNION
SELECT '1003' ID, 'C,E,G' STR FROM DUAL
)
select id, substr(STR, instr(STR, ',', 1, lvl) + 1, instr(STR, ',', 1, lvl + 1) - instr(STR, ',', 1, lvl) - 1) name
from
( select ',' || STR || ',' as STR, id from TAB ),
( select level as lvl from dual connect by level <= 100 )
where lvl <= length(STR) - length(replace(STR, ',')) - 1
order by ID, NAME
这篇关于通过 Oracle SQL 查询将行中的列的逗号分隔值拆分的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!
本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!