如果你比较

SQL> with
  2  test (col) as
  3    -- sample string
  4    (select 'AAA,BBB,CCC,DDD' from dual)
  5  select regexp_substr(col, '[^,]+', 1, level) val
  6  from test
  7  connect by level <= regexp_count(col, ',') + 1;

VAL
------------------------------------------------------------
AAA
BBB
CCC
DDD

SQL>

字符串
不使用connect by子句的程式码

SQL> with
  2  test (col) as
  3    -- sample string
  4    (select 'AAA,BBB,CCC,DDD' from dual),
  5  comma (col) as
  6    -- add comma AFTER the COL string so that code that follows is simpler to maintain
  7    (select col || ',' from test),
  8  temp (val, str) as
  9    -- recursion
 10    (select substr (col, 1, instr (col, ',') - 1) as val,
 11            substr (col, instr (col, ',') + 1) as str
 12     from comma
 13     where col like '%,%'
 14     --
 15     union all
 16     --
 17     select substr (str, 1, instr (str, ',') - 1),
 18            substr (str, instr (str, ',') + 1)
 19     from temp
 20     where str like '%,%'
 21    )
 22  select val
 23  from temp
 24  where val is not null;

VAL
--------------------------------------------------------------------------------
AAA
BBB
CCC
DDD

SQL>

型
问题是：你为什么要这么做

您可以用途：

WITH bounds ( value, start_pos, end_pos ) AS (
  SELECT value,
         1,
         INSTR( value, ',' )
  FROM   table_name
UNION ALL
  SELECT value,
         end_pos + 1,
         INSTR( value, ',', end_pos + 1 )
  FROM   bounds
  WHERE  end_pos > 0
)
SELECT CASE end_pos
       WHEN 0
       THEN SUBSTR(value, start_pos )
       ELSE SUBSTR(value, start_pos, end_pos - start_pos )
       END AS value
FROM   bounds;

字符串
在每次递归中，你只找到字符串中下一个逗号的位置，然后从上一个逗号之后的位置开始查找：
1.不使用（慢）正则表达式;
1.不会从字符串的开头开始重复计算分隔符的个数（因此不必在每次递归时重复工作）;和/或
1.直到查询的最后一个非递归部分才执行字符串操作。
所以，according to the analysis performed in this question，它可能比使用分层查询更快。
对于样本数据：

CREATE TABLE table_name (value) AS
SELECT 'AAA,BBB,CCC,DDD' FROM DUAL;

型
输出：
| VALUE |
| ------------ |
| AAA |
| BBB |
| CCC |
| DDD |
fiddle的