sql - Split string oracle into a single column and insert into a table

Question

I have a table with column data in below format(Row# just to indicate row number).

Row#1 :test.doc#delimiter#1234,test1.doc#delimiter#1235,test2.doc#delimiter#1236<br>
Row#2 :fil1.txt#delimiter#1456,fil1.txt#delimiter#1457

I want to split the string using comma(,) as delimiter, list all in one single column and insert into a new table.

output should be something like this (Row# just to indicate row number):

Row#1:test.doc#delimiter#1234<br>
Row#2:test1.doc#delimiter#1235<br>
Row#3:test2.doc#delimiter#1236<br>
Row#4: fil1.txt#delimiter#1456

Can anyone help me do this?

Answer 1

WITH data AS (
  SELECT 'test.doc#delimiter#1234,test1.doc#delimiter#1235,test2.doc#delimiter#1236' AS "value" FROM DUAL
  UNION ALL
  SELECT 'fil1.txt#delimiter#1456,fil1.txt#delimiter#1457' AS "value" FROM DUAL
)
SELECT  REGEXP_SUBSTR( data."value", '[^,]+', 1, levels.COLUMN_VALUE )
FROM    data,
        TABLE(
          CAST(
            MULTISET(
              SELECT LEVEL
              FROM   DUAL
              CONNECT BY  LEVEL <= LENGTH( regexp_replace( "value", '[^,]+'))  + 1
            ) AS sys.OdciNumberList
          )
        ) levels;