在Oracle中拆分逗号分隔的值 [英] Splitting comma separated values in Oracle
问题描述
我的数据库中有一个列,其中的值如下所示:
I have column in my database where the values are coming like the following:
3862,3654,3828
在虚拟列中没有任何编号.逗号分隔的值可以出现.我尝试使用以下查询,但它正在创建重复的结果.
In dummy column any no. of comma separated values can come. I tried with following query but it is creating duplicate results.
select regexp_substr(dummy,'[^,]+',1,Level) as dummycol
from (select * from dummy_table)
connect by level <= length(REGEXP_REPLACE(dummy,'[^,]+'))+1
我不了解这个问题.谁能帮忙?
I am not understanding the problem. Can anyone can help?
推荐答案
对我来说很完美-
SQL> WITH dummy_table AS(
2 SELECT '3862,3654,3828' dummy FROM dual
3 )
4 SELECT trim(regexp_substr(dummy,'[^,]+',1,Level)) AS dummycol
5 FROM dummy_table
6 CONNECT BY level <= LENGTH(REGEXP_REPLACE(dummy,'[^,]+'))+1
7 /
DUMMYCOL
--------------
3862
3654
3828
SQL>
还有许多其他方法可以实现它.阅读分割单逗号分隔的字符串分成行.
There are many other ways of achieving it. Read Split single comma delimited string into rows.
更新关于使用列而不是单个字符串值时的重复项.在PRIOR子句中看到使用DBMS_RANDOM摆脱循环循环此处
Update Regarding the duplicates when the column is used instead of a single string value. Saw the use of DBMS_RANDOM in the PRIOR clause to get rid of the cyclic loop here
尝试以下操作,
SQL> WITH dummy_table AS
2 ( SELECT 1 rn, '3862,3654,3828' dummy FROM dual
3 UNION ALL
4 SELECT 2, '1234,5678' dummy FROM dual
5 )
6 SELECT trim(regexp_substr(dummy,'[^,]+',1,Level)) AS dummycol
7 FROM dummy_table
8 CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(dummy,'[^,]+'))+1
9 AND prior rn = rn
10 AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
11 /
DUMMYCOL
--------------
3862
3654
3828
1234
5678
SQL>
更新2
另一种方式,
SQL> WITH dummy_table AS
2 ( SELECT 1 rn, '3862,3654,3828' dummy FROM dual
3 UNION ALL
4 SELECT 2, '1234,5678,xyz' dummy FROM dual
5 )
6 SELECT trim(regexp_substr(t.dummy, '[^,]+', 1, levels.column_value)) AS dummycol
7 FROM dummy_table t,
8 TABLE(CAST(MULTISET
9 (SELECT LEVEL
10 FROM dual
11 CONNECT BY LEVEL <= LENGTH (regexp_replace(t.dummy, '[^,]+')) + 1
12 ) AS sys.OdciNumberList)) LEVELS
13 /
DUMMYCOL
--------------
3862
3654
3828
1234
5678
xyz
6 rows selected.
SQL>
这篇关于在Oracle中拆分逗号分隔的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!