CODEWITHSUNDEEP
sqlstringoracle-databasereplaceregexp-replace
Jay
Jay

Reputation: 77

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

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'

Upvotes: 0

Views: 1181

Answers (2)

user5683823
user5683823

Reputation:

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

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 143003

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>

Upvotes: 0

Related Questions