Oracle : SQL to replace items in a string with items of another string

Question

I have a column "col1" value like : 'a,b,x,y,z' (ordered string)

Another column "col2" is like : 'a,x' or 'b,y,z' (ordered string)

All the string values in "col2" are generated by a subquery. So it is not constant.

But, the value in "col1" is constant. That is col1='a,b,x,y,z'

create table test (col1 varchar2(20), col2 varchar2(10)); 

insert into test values ('a,b,x,y,z','a,x');

insert into test values ('a,b,x,y,z','b,y,z');

Need help with the replacing in one sql.

Need help to replace the elements on "col1" with "col2".

For example,

when col2='a,x', the result should be : 'b,y,z'

when col2='b,y,z', the result should be : 'a,x'

user5683823 · Accepted Answer · 2018-11-29 19:02:01Z

2

Here is a fun way to do this:

select col1, col2,
       ltrim(regexp_replace(translate(replace(col1,','),','||replace(col2,','),',')
                            ,'(.)',',\1'),',') as col3
from   test;

That is: (reading the function calls as they are executed, from inside out)

Remove the commas from both strings
Use TRANSLATE() to remove the characters of the second string from the first string
Use REGEXP_REPLACE to add commas before each character of the remaining string
Trim the leading comma

answered Nov 29, 2018 at 19:02

user5683823

Sign up to request clarification or add additional context in comments.

Comments

Littlefoot · Accepted Answer · 2018-11-29 18:18:38Z

Here's one option; I included the ID column to make it simpler. I hope that a column similar to it exists in your real case.

The idea is:

split each column (col1, col2) to rows
- CTE one represents col1's rows
- CTE two represents col2's rows
using the MINUS set operator, subtract those two sets of rows
using LISTAGG, aggregate the result

SQL> select * From test;

        ID COL1                 COL2
---------- -------------------- ----------
         1 a,b,x,y,z            a,x
         2 a,b,x,y,z            b,y,z

SQL> with
  2  one as
  3    (select id, regexp_substr(col1, '[^,]+', 1, column_value) col
  4       from test,
  5            table(cast(multiset(select level from dual
  6                                connect by level <= regexp_count(col1, ',') + 1
  7                               ) as sys.odcinumberlist))
  8    ),
  9  two as
 10    (select id, regexp_substr(col2, '[^,]+', 1, column_value) col
 11       from test,
 12            table(cast(multiset(select level from dual
 13                                connect by level <= regexp_count(col2, ',') + 1
 14                               ) as sys.odcinumberlist))
 15    ),
 16  t_minus as
 17    (select id, col from one
 18     minus
 19     select id, col from two
 20    )
 21  select id, listagg(col, ',') within group (order by col) result
 22  From t_minus
 23  group by id;

        ID RESULT
---------- --------------------
         1 b,y,z
         2 a,x

SQL>

Collectives™ on Stack Overflow

Oracle : SQL to replace items in a string with items of another string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related