Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
0


WITH SAMPLE_T AS (
  SELECT 'A,BC,,EF' AS VAL FROM DUAL UNION ALL
  SELECT 'A,BC,D,EF' AS VAL FROM DUAL 
)
SELECT A.VAL
      ,REGEXP_SUBSTR(A.VAL,'[^,]+',1,1) AS COL1
      ,REGEXP_SUBSTR(A.VAL,'[^,]+',1,2) AS COL2
      ,REGEXP_SUBSTR(A.VAL,'[^,]+',1,3) AS COL3
      ,REGEXP_SUBSTR(A.VAL,'[^,]+',1,4) AS COL4
 FROM SAMPLE_T A;


VAL
COL1
COL2
COL3
COL4


A,BC,,EF
A
BC
EF
null

A,BC,D,EF
A
BC
D
EF


WITH SAMPLE_T AS (
  SELECT 'A,BC,,EF' AS VAL FROM DUAL UNION ALL
  SELECT 'A,BC,D,EF' AS VAL FROM DUAL 
)
SELECT A.VAL
      ,TRIM(REGEXP_SUBSTR(REPLACE(A.VAL,',',', '),'[^,]+',1,1)) AS COL1
      ,TRIM(REGEXP_SUBSTR(REPLACE(A.VAL,',',', '),'[^,]+',1,2)) AS COL2
      ,TRIM(REGEXP_SUBSTR(REPLACE(A.VAL,',',', '),'[^,]+',1,3)) AS COL3
      ,TRIM(REGEXP_SUBSTR(REPLACE(A.VAL,',',', '),'[^,]+',1,4)) AS COL4
 FROM SAMPLE_T A;


VAL
COL1
COL2
COL3
COL4


A,BC,,EF
A
BC
null
EF

A,BC,D,EF
A
BC
D
EF


WITH SAMPLE_T AS (
  SELECT 'A,BC,,EF' AS VAL FROM DUAL UNION ALL
  SELECT 'A,BC,D,EF' AS VAL FROM DUAL 
)
SELECT A.VAL
      ,REGEXP_SUBSTR(A.VAL,'[^,]',1,1) AS COL1
      ,REGEXP_SUBSTR(A.VAL,'[^,]+',1,2) AS COL2
      ,REGEXP_SUBSTR(A.VAL,'(.*?)(,|$)',1,3,null,1) AS COL3
      ,REGEXP_SUBSTR(A.VAL,'(.*?)(,|$)',1,4,null,1) AS COL4
 FROM SAMPLE_T A;


VAL
COL1
COL2
COL3
COL4


A,BC,,EF
A
BC
null
EF

A,BC,D,EF
A
BC
D
EF


WITH SAMPLE_T AS (
  SELECT 'A,BC' AS VAL  FROM DUAL UNION ALL
  SELECT 'D,E,F' AS VAL FROM DUAL
)
SELECT A.VAL
      ,REGEXP_SUBSTR(A.VAL,'[^,]+',1, B.LNO) AS COL1
 FROM (SELECT VAL, REGEXP_COUNT(VAL,'[^,]+') AS STR_COUNT 
        FROM SAMPLE_T) A
     ,(SELECT LEVEL LNO
               FROM DUAL
         CONNECT BY LEVEL <= 10 /* REGEXP_COUNT 값의 최대값을 주면 되나, 큰 값을 주면 됨. */
      ) B 
 WHERE A.STR_COUNT >= B.LNO 


VAL
COL1


A,BC
A

D,E,F
D

A,BC
BC

D,E,F
E

D,E,F
F


WITH SAMPLE_T AS (
  SELECT 1 AS NO, 'A,BC' AS VAL FROM DUAL UNION ALL
  SELECT 2 AS NO, 'D,E,F' AS VAL FROM DUAL
)
SELECT A.VAL
      ,REGEXP_SUBSTR(A.VAL,'[^,]+',1, LNO) AS COL1
 FROM SAMPLE_T A
     ,LATERAL(SELECT LEVEL LNO
               FROM DUAL
         CONNECT BY LEVEL <= REGEXP_COUNT(A.VAL,'[^,]+'))


VAL
COL1


A,BC
A

A,BC
BC

D,E,F
D

D,E,F
E

D,E,F
F


WITH SAMPLE_T AS (
  SELECT ','||'A,BC,,EF'||',' AS VAL FROM DUAL 
)
SELECT A.VAL, LEVEL LNO
      ,SUBSTR(A.VAL,INSTR(VAL,',',1,LEVEL)+1, INSTR(VAL,',',1,LEVEL+1)-INSTR(VAL,',',1,LEVEL)-1) AS COL1
 FROM SAMPLE_T A
 CONNECT BY LEVEL <= 4


VAL
LNO
COL1


,A,BC,,EF,
1
A

,A,BC,,EF,
2
BC

,A,BC,,EF,
3
null

,A,BC,,EF,
4
EF


WITH SAMPLE_T AS (
  SELECT ','||'A,,EF'||',' AS VAL FROM DUAL UNION ALL 
  SELECT ','||'D,G'||',' AS VAL FROM DUAL 
)
SELECT A.VAL, LNO,
       SUBSTR(VAL,INSTR(VAL,',',1,LNO)+1, INSTR(VAL,',',1,LNO+1)-INSTR(VAL,',',1,LNO)-1) AS COL1
 FROM SAMPLE_T A
     ,LATERAL(SELECT LEVEL LNO
                FROM DUAL
              CONNECT BY LEVEL <= (LENGTH(A.VAL)-LENGTH(REPLACE(A.VAL,','))-1)
              )    


VAL
LNO
COL1


,A,,EF,
1
A

,A,,EF,
2
null

,A,,EF,
3
EF

,D,G,
1
D

,D,G,
2
G