非常适合我-
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>
还有许多其他方法可以实现它。阅读将单个逗号分隔的字符串拆分成行。
。只见PRIOR子句中使用DBMS_RANDOM的摆脱循环回路的在这里
尝试以下方法
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>
其他方式，
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>
602392714
清零编程群