By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test_data ( ID, NAME, ColumnA, ColumnB ) AS
SELECT 1, 'ITEM1', ',2562,2563,2564', ',121,122,123' FROM DUAL UNION ALL
SELECT 2, 'ITEM2', NULL, ',6455,545' FROM DUAL UNION ALL
SELECT 3, 'ITEM3', ',1221,1546', NULL FROM DUAL UNION ALL
SELECT 4, 'ITEM4', NULL, NULL FROM DUAL;
4 rows affected
WITH split_data ( id, name, columna, columnb, starta, enda, startb, endb ) AS (
SELECT id,
name,
columna,
columnb,
INSTR(columna,',',1,1),
INSTR(columna,',',1,2),
INSTR(columnb,',',1,1),
INSTR(columnb,',',1,2)
FROM test_data
UNION ALL
SELECT id,
name,
columna,
columnb,
enda,
CASE WHEN enda = 0 THEN 0 ELSE INSTR(columna,',',enda+1,1) END,
endb,
CASE WHEN endb = 0 THEN 0 ELSE INSTR(columnb,',',endb+1,1) END
FROM split_data
WHERE enda > 0
OR endb > 0
)
SELECT id,
name,
CASE
WHEN starta = 0 THEN NULL
WHEN enda = 0 THEN SUBSTR( columna, starta + 1 )
ELSE SUBSTR( columna, starta + 1, enda - starta - 1 )
END AS valuea,
CASE
WHEN startb = 0 THEN NULL
WHEN endb = 0 THEN SUBSTR( columnb, startb + 1 )
ELSE SUBSTR( columnb, startb + 1, endb - startb - 1 )
END as valueb
FROM split_data
| ID | NAME | VALUEA | VALUEB |
|---|---|---|---|
| 1 | ITEM1 | 2562 | 121 |
| 1 | ITEM1 | 2563 | 122 |
| 1 | ITEM1 | 2564 | 123 |
| 2 | ITEM2 | null | 6455 |
| 2 | ITEM2 | null | 545 |
| 3 | ITEM3 | 1221 | null |
| 3 | ITEM3 | 1546 | null |
| 4 | ITEM4 | null | null |