sql - Monitoring data with delimiter to create a new rows

Question

I want to create a rows from one row on table that contains a delimiter on some fields as mentioned below on screen shoot

I want a result A separalte rows for the rows that already contains a deleimiter ; the inputs are data from table 1 and the output is data as mentionned below on table 2 using oracle sql :insert and select query

you can see below the output recommanded:

标签： sqloracle

Answer 1

或者：

SQL> with test (id, description, val, after, before) as
  2    -- you already have sample data and don't type this
  3    (select 1, 'ARTIC1;ARTIC2;ART11', '15;2;3', '12;6;8', '13;7;12' from dual union all
  4     select 2, 'ARTICLE3;ARTICLE4'  , '3;5'   , '10;23' , '12;25'   from dual union all
  5     select 3, 'ARTICLE 5'          , '6'     , '2'     , '1.9'     from dual
  6    )
  7  -- query that does the job begins here
  8  select id,
  9    regexp_substr(description, '[^;]+', 1, column_value) descr,
 10    regexp_substr(val        , '[^;]+', 1, column_value) val,
 11    regexp_substr(after      , '[^;]+', 1, column_value) after,
 12    regexp_substr(before     , '[^;]+', 1, column_value) before
 13  from test cross join
 14    table(cast(multiset(select level from dual
 15                        connect by level <= regexp_count(description, ';') + 1
 16                       ) as sys.odcinumberlist))
 17  order by id, descr, val;

        ID DESCR      VAL        AFTER      BEFORE
---------- ---------- ---------- ---------- ----------
         1 ARTIC1     15         12         13
         1 ARTIC2     2          6          7
         1 ART11      3          8          12
         2 ARTICLE3   3          10         12
         2 ARTICLE4   5          23         25
         3 ARTICLE 5  6          2          1.9

6 rows selected.

SQL>