Oracle SQL: Return each CSV value as a row

Question

I have values in CSV format in a column of a table. One set of values could look like this:

44,27,68,57,(...),77

I want to create a query statement in Oracle SQL that returns this columns values like this (with each separated value being its own row):

44
--
27
--
(...)
--
77

How would I achieve this?

Answer 1

WITH DATA AS
      ( SELECT '44,27,68,57,(...),77' str FROM dual
      )
    SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
    FROM DATA
    CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0
    /

DEMO

Or for your case :

SELECT trim(regexp_substr(your_column_name, '[^,]+', 1, LEVEL)) str
FROM YOUR_TABLE_NAME
CONNECT BY instr(your_column_name, ',', 1, LEVEL - 1) > 0