Split a string using REGEXP and CONNECT BY...

29/11/22 11:42

This one having the advantage of not using the regexp_substr in the where clause,and - when using the techniques on actual tables - to avoid the usage of "prior sys_guid() is not null"

SELECT column_value AS id, REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, column_value) AS data
FROM DUAL
CROSS JOIN
  TABLE(CAST(MULTiSET(
    SELECT LEVEL FROM DUAL
      CONNECT BY LEVEL <= regexp_count('A,B,C,D', ',') + 1
   ) AS sys.odcinumberlist)
  )
;