Oracle query to split 1 row into two where conditions are applicable The scenario question:

"I'm having difficulties with splitting a single row into two individual ones."

My test schema is:

CREATE TABLE T
    (id int, 
     old varchar2(24), 
     new varchar2(24))
;


where old and new are strings that need to be split, an example row:

  INTO T (id, old, new)
     VALUES (14, 'L-D / T-E', 'L-E / T-E')
  INTO T (id, old, new)
     VALUES (14, 'L-D / T-E', 'L-D / T-D')
  INTO T (id, old, new)
     VALUES (12, 'L-D / T-E', 'L-E / T-D')


In this example 'L' has changed in row 1, 'T' has changed in row 2 and both have changed in row 3.

The table is:


ID    OLD         NEW
14    L-D / T-E   L-E / T-E
14    L-D / T-E   L-D / T-D
12    L-D / T-E   L-E / T-D



I'm trying to split these so that the resulting table will have individual rows for each significant change such that the result will look like:

ID  OLD     NEW
1   L-D     L-E  //for existing row 1
2   T-E     T-D  //for existing row 2
//then two rows for existing row 3
3   L-D     L-E 
4   T-E     T-D


Once these rows have been inserted, I'd then like to delete the existing rows.



My solution looks like:

    drop table t;
CREATE TABLE T
    (id int, 
   key int,
  old varchar2(24), 
  new varchar2(24))
;
INSERT ALL 
      INTO T (id, key, old, new)
         VALUES (12, 638, 'L-D / T-E', 'L-E / T-E')
      INTO T (id, key, old, new)
         VALUES (13, 638, 'L-D / T-E', 'L-D / T-D')
      INTO T (id, key ,old, new)
         VALUES (14, 638, 'L-D / T-E', 'L-E / T-D')      
SELECT * FROM dual;
SELECT * FROM T;
--insert into t (id, old, new) values (1,'dasdsad', 'asdasd');

BEGIN
  INSERT INTO t (id, key, old, new) 
      WITH DATA AS
      (SELECT id, old, new
      FROM t
      WHERE 
      --1 change in t 
      ((SUBSTR(OLD,3,1) = SUBSTR(NEW, 3,1)
        AND SUBSTR(OLD, 9) &lt;&gt; SUBSTR(NEW, 9)
        )) )--end select,
    SELECT id, 646, substr(old, 7, 9) old,  
               substr(new, 7, 9) new

    FROM DATA
      CONNECT BY  level = 1; 

    INSERT INTO t (id, key, old, new) 
      WITH DATA AS
      (SELECT id,OLD, new
      FROM t
      WHERE 
      --1 change in t 
      ((SUBSTR(OLD,3,1) &lt;&gt; SUBSTR(NEW, 3,1)
        AND SUBSTR(OLD, 9) = SUBSTR(NEW, 9)
        )) )--end select,
    SELECT id, 645, substr(old, 1, 3) old,  
               substr(new, 1, 3) new

    FROM DATA
      CONNECT BY  level = 1; 

    INSERT INTO t (id,key,old, new) 
      WITH DATA AS
      (SELECT id,OLD, new
      FROM t
      WHERE 
      --BOLLOX, need two keys, one for each row.  
      ((SUBSTR(OLD,3,1) &lt;&gt; SUBSTR(NEW, 3,1)
        AND SUBSTR(OLD, 9) &lt;&gt; SUBSTR(NEW, 9)
        )) )--end select,
    SELECT id, 
          (644+level), --will result in 645 and 646 --needs testing
          trim(regexp_substr(OLD, '[^/]+', 1, LEVEL)) OLD,  
          trim(regexp_substr(NEW, '[^/]+', 1, LEVEL)) NEW
    FROM DATA
      CONNECT BY (LEVEL &lt;= regexp_count(new, '/')+1);

      delete from t where key = 638;
END;
 /


It's not very elegant, so any advice, critique and suggestions are welcome.
I'd solve the problem by providing a PL/SQL procedure that knows the "split"-logic.

In the example below I assume that non-splitted rows are not inserted into the table at all but all inserts should be done with the procedure. This is a rather common pattern when there's data manipulations but it is not clear from your problem statement if this is an acceptable solution in your case.

I used regular expressions as it's a very handy tool for pattern matching with superior expressiveness and clarity compared to substr(instr()) spaghetti.

I didn't used triggers as it's not possible to modify the table trigger is attached to.

The solution can be easily applied even if you need to read the input data (i.e. non-splitted) from the same table.

Example

create table cr1_t(
 id number
,orig_id number
,old varchar2(24)
,new varchar2(24)
);

create sequence cr1_s;

create or replace procedure cr1_insert(
  p_id in number
 ,p_old in varchar2
 ,p_new in varchar2
) is
  -- the p_*-variables are visible in the nested subprogram
  procedure insert_if(p_pattern in varchar2) is
    v_old constant varchar2(32767) := regexp_substr(p_old, p_pattern);
    v_new constant varchar2(32767) := regexp_substr(p_new, p_pattern);
  begin
    -- insert only if the values are different
    if v_old &lt;&gt; v_new
    then 
      insert into cr1_t(id, orig_id, old, new) values (cr1_s.nextval, p_id, v_old, v_new);
    end if;
  end;
begin
  -- https://en.wikipedia.org/wiki/Tuple
  -- note the separator doesn't matter

  -- the pattern will match the first member of a "2-tuple"
  -- regexp explained:
  -- ^           anchor the pattern to the beginning of a string
  -- [[:upper:]] any uppercase character
  -- -           character '-'
  -- [[:upper:]] any uppercase character
  -- 
  insert_if(p_pattern =&gt; '^[[:upper:]]-[[:upper:]]');

  -- the pattern will match the second member of a "2-tuple"
  -- (T-[[:upper:]])$
  -- regexp is similar than above but now the search is anchored to the end of the string ($):
  insert_if(p_pattern =&gt; '[[:upper:]]-[[:upper:]]$');  
end;
/


Test run

begin
  for i in (with data(id, old, new) as (
              select 10, 'L-D / T-E', 'L-E / T-E' from dual union all
              select 20, 'L-D / T-E', 'L-D / T-D' from dual union all
              select 30, 'L-D / T-E', 'L-E / T-D' from dual union all
              select 40, 'A-B | C-D', 'A-E | C-D' from dual union all
              select 50, 'L-D / T-E', 'L-D / T-E' from dual
            ) select * from data)
  loop
    cr1_insert(i.id, i.old, i.new);
  end loop;
end;
/

select * from cr1_t;

ID ORIG_ID OLD NEW
-- ------- --- ---
 1      10 L-D L-E
 2      20 T-E T-D
 3      30 L-D L-E
 4      30 T-E T-D
 5      40 A-B A-E