SQL: Delimited Strings to rows

Problem

You have data (example numbers) that is delimited, and you want to split it up into rows so that you can join to it.

Solution

Adapt this query to separate the values out.

with temp as

(

    select release_id, environment_id, files, file_type  from apex_ebs_extension.ds_release_results_files_v

)

select distinct

  release_id, environment_id,file_type,

  trim(regexp_substr(t.files, '[^,]+', 1, levels.column_value))  as files

from

  temp t,

  table(cast(multiset(select level from dual connect by  level <= length (regexp_replace(t.files, '[^,]+'))  + 1) as sys.OdciNumberList)) levels

Result

Acknowledgment

http://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle