在connectby子句中可以使用正则表达式。
下面的示例可能会对您有所帮助-

WITH data AS (
    SELECT
        'a,b,c,d' str
    FROM
        dual
)
SELECT
    regexp_substr(str, '[^,]+', 1, level) col
FROM
    data
CONNECT BY
    level <= regexp_count(str, ',') + 1;

或者让我简单一点

SELECT
    regexp_substr('a,b,c,d', '[^,]+', 1, level) col
FROM
    dual
CONNECT BY
    level <= regexp_count('a,b,c,d', ',') + 1;

如需更多说明，请查看此

最简单的方法是使用xmltable和“tokenize”函数：
例如，您的表是t，列名是str：

select * 
from t, 
     xmltable('tokenize(., ",")' 
              passing t.str 
              columns 
                n for ordinality,
                substring varchar2(10) path '.'
              ) x

“tokenize”函数的第一个参数是字符串，第二个参数是分隔符。点（.）是你的弦 passing 条款如下： tokenize(., ",") 完整示例：

with t(str) as (select 'a,b,c,d' str from dual)
select * 
from t, 
     xmltable('tokenize(., ",")' 
              passing t.str 
              columns 
                n for ordinality,
                substring varchar2(10) path '.'
              ) x;

结果：

STR              N SUBSTRING
------- ---------- ----------
a,b,c,d          1 a
a,b,c,d          2 b
a,b,c,d          3 c
a,b,c,d          4 d

容易的

SQL> with t as (
  2  select 'a' as id from dual union all
  3  select 'b' as id from dual union all
  4  select 'c' as id from dual )
  5* select listagg(id,',') within group (order by id) from t
SQL> /

LISTAGG(ID,',')WITHINGROUP(ORDERBYID)
--------------------------------------------------------------------------------
a,b,c

在sql server中：

Select value from STRING_SPLIT('a,b,c,d', ',');

以下方法应该有效

create table t(col1 varchar2(10))

insert 
  into t
select 'a,b,c,d' 
  from dual 

with data
  as (select ','||col1||',' as col1
       from t
      )
 select substr(col1
              ,instr(col1,',',1,level)+1
              ,instr(col1,',',1,level+1)
               -
               instr(col1,',',1,level)
               -1
               )
        ,level 
   from data
connect by level<=length(col1)-length(replace(col1,','))-1
order by 2

+------------+-------+
| COL_VALUES | LEVEL |
+------------+-------+
| a          |     1 |
| b          |     2 |
| c          |     3 |
| d          |     4 |
+------------+-------+

db小提琴链接https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=0686f9015e0ceb56c12a50349d6b5efd

select substr(src, start_pos, decode(end_pos, 0, length(src)+1, end_pos)-start_pos) val
from (
    select src,
        level lvl,
        decode(level, 1, 1, instr(src, ',', 1, level-1)+1) start_pos,
        instr(src, ',', 1, level) end_pos
    from (select 'a,bb,ccc,dddd' src from dual)
    connect by instr(src, ',', 1, level-1) > 0
)