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 |