Splitting every row of varchar column and create new row for every split part

Question

I have a table called "Table" and attribute named "History". This history attribute has values like:

   1 Finished (30-05-2018);
   2 InProgress (25-05-2018); Rejected(26-05-2018); InProgress (28-05-2018); Finished (30-05-2018);
   3 InProgress (25-05-2018); Finished (30-05-2018);

I want to split this attribute by the semicolumn (;) and create a new row for every history part. So for the rows in the code/example above it should create 7 rows. I have managed to do this for one row by the code below. The problem is that I want to do this for every row in this table. Here occurs a problem: when I remove the WHERE condition in the WITH I get way to many resultrows and also a lot of NULL values. What am I doing wrong?

WITH DATA AS
   ( SELECT "WorkID" w,"History" his FROM Table
   where "WorkID" = 75671
   )
   SELECT w, trim(regexp_substr(his, '[^;]+', 1, LEVEL)) his
   FROM DATA
   CONNECT BY regexp_substr(his , '[^;]+', 1, LEVEL) IS NOT NULL

score 1 · Accepted Answer · answered Nov 27 '18 at 16:33

Here's a full working example where the regex looks for a delimiter of a semi-colon followed by a space OR the end of the line:

SQL> WITH Tbl(WorkID, History) AS(
     select 1, 'Finished (30-05-2018);' from dual union all
     select 2, 'InProgress (25-05-2018); Rejected(26-05-2018); InProgress (28-05-2018); Finished (30-05-2018);' from dual union all
     select 3, 'InProgress (25-05-2018); Finished (30-05-2018);' from dual
   )
   select WorkID, regexp_substr(History, '(.*?)(; |;$)', 1, level, NULL, 1) history
   from Tbl
   connect by regexp_substr(History, '(.*?)(; |;$)', 1, level) is not null
   and prior WorkID = WorkID
   and prior sys_guid() is not null;

    WORKID HISTORY
---------- -------------------------
         1 Finished (30-05-2018)
         2 InProgress (25-05-2018)
         2 Rejected(26-05-2018)
         2 InProgress (28-05-2018)
         2 Finished (30-05-2018)
         3 InProgress (25-05-2018)
         3 Finished (30-05-2018)

7 rows selected.

score 0 · Answer 2 · answered Nov 27 '18 at 14:00

If you want to use a connect by against many rows to split a column into multiple rows, you need to have a couple of extra things - a means of telling Oracle to loop over the same row, and a check to make sure it knows which prior row to use.

That means your query would become something like:

select "WorkID" w,
       "History" his
from table
connect by prior "WorkID" = "WorkID" -- I'm assuming that "WorkID" uniquely identifies a row; if not, add in the additional columns that identify a row
and regexp_substr("History", '[^;]+', 1, level) is not null
and prior sys_guid() is not null;

Splitting every row of varchar column and create new row for every split part

2 Answers2