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'

Answer 1

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