I have table with column having data in below format in Oracle DB.

I am trying to convert the data in below format

Basically trying to get everything after : and before ; to move it substitute comma with it.

I tried some SUBSTR and LISTAGG but couldn't get anything worth sharing.

Regards.

CodePudding user response：

Here's one option; read comments within code.

SQL> with test (id, col) as
  2    -- sample data
  3    (select 1, 'abc,mno:EMP'              from dual union all
  4     select 2, 'xyz:EMP;tyu,opr:PROF'     from dual union all
  5     select 3, 'abc,mno:EMP;tyu,opr:PROF' from dual
  6    ),
  7  temp as
  8    -- split sample data to rows
  9    (select id,
 10       column_value cv,
 11       regexp_substr(col, '[^;] ', 1, column_value) val
 12     from test cross join
 13       table(cast(multiset(select level from dual
 14                           connect by level <= regexp_count(col, ';')   1
 15                          ) as sys.odcinumberlist))
 16    )
 17  -- finally, replace comma with a string that follows a colon sign
 18  select id,
 19    listagg(replace(val, ',', substr(val, instr(val, ':')) ||';'), ';') within group (order by cv) new_val
 20  from temp
 21  group by id
 22  order by id;

        ID NEW_VAL
---------- ----------------------------------------
         1 abc:EMP;mno:EMP
         2 xyz:EMP;tyu:PROF;opr:PROF
         3 abc:EMP;mno:EMP;tyu:PROF;opr:PROF

SQL>

CodePudding user response：

Using the answer of littlefoot, if i were to use cross apply i wouldnt need to cast as multiset...

with test (id, col) as
      -- sample data
      (select 1, 'abc,mno:EMP'              from dual union all
       select 2, 'xyz:EMP;tyu,opr:PROF'     from dual union all
       select 3, 'abc,mno:EMP;tyu,opr:PROF' from dual
      ),
    temp as
      -- split sample data to rows
      (select id,
              column_value cv,
              regexp_substr(col, '[^;] ', 1, column_value) val
        from test 
        cross apply (select level as column_value
                       from dual
                      connect by level<= regexp_count(col, ';')   1)       
     )
   -- finally, replace comma with a string that follows a colon sign
   select id,
          listagg(replace(val, ',', substr(val, instr(val, ':')) ||';'), ';') within group (order by cv) new_val
     from temp
   group by id
   order by id;

CodePudding user response：

You do not need recursive anything, just basic regex: if the pattern is always something,something2:someCode (e.g. you have no colon before the comma), then it would be sufficient.

with test (id, col) as (
  select 1, 'abc,mno:EMP'              from dual union all
  select 2, 'xyz:EMP;tyu,opr:PROF'     from dual union all
  select 3, 'abc,mno:EMP;tyu,opr:PROF' from dual union all
  select 3, 'abc,mno:EMP;tyu,opr:PROF;something:QWE;something2:QWE' from dual
)
select
  /*
    Grab this groups:
    1) Everything before the comma
    2) Then everything before the colon
    3) And then everything between the colon and a semicolon
    
    Then place group 3 between 1 and 2
  */
  trim(trailing ';' from regexp_replace(col || ';', '([^,] ),([^:] ):([^;] )', '\1:\3;\2:\3')) as res
from test

| RES                                                            |
| :------------------------------------------------------------- |
| abc:EMP;mno:EMP                                                |
| xyz:EMP;tyu:PROF;opr:PROF                                      |
| abc:EMP;mno:EMP;tyu:PROF;opr:PROF                              |
| abc:EMP;mno:EMP;tyu:PROF;opr:PROF;something:QWE;something2:QWE |

db<>fiddle here