By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name(list VARCHAR2(100));
INSERT INTO table_name (list) VALUES ( '"a","b","c","d"' );
1 rows affected
INSERT INTO table_name (list) VALUES ( '"noquotes","""quotes""","comma,here"' );
1 rows affected
WITH matches (list, match, epos) AS (
SELECT list,
REPLACE(REGEXP_SUBSTR(list, '"(([^"]|"")+)"(,|$)', 1, 1, NULL, 1), '""', '"'),
REGEXP_INSTR(list, '"(([^"]|"")+)"(,|$)', 1, 1, 1)
FROM table_name
UNION ALL
SELECT list,
REPLACE(REGEXP_SUBSTR(list, '"(([^"]|"")+)"(,|$)', epos, 1, NULL, 1), '""', '"'),
REGEXP_INSTR(list, '"(([^"]|"")+)"(,|$)', epos, 1, 1)
FROM matches
WHERE epos > 0
AND epos < LENGTH(list)
)
SEARCH DEPTH FIRST BY list SET order_id
SELECT match
FROM matches
| MATCH |
|---|
| a |
| b |
| c |
| d |
| noquotes |
| "quotes" |
| comma,here |