how to create new column from difference between two columns?

Question

I create new table using join,listagg etc. I determined two columns as follow using listagg function. That columns are included following information.

Listagg_1_Column | Listagg_2_Column
---------------- |-----------------
123,234,565,321  |  123,234

I want to add new column this table. New column is included difference between Listagg_1_Column and Listagg_2_Column as below

New_Column
----------
565,321

For this, i used replace(Listagg_1_Column,Listagg_2_Column,'') statement but i retrieved wrong consequences. How to get new column by changing query? Any help will be appreciated. Thanks in advance.

Answer 1

I you have the test data:

CREATE TABLE data ( col1, col2 ) AS
SELECT '123,234,565,321', '123,234' FROM DUAL UNION ALL
SELECT '123,234,1123,2345', '123,234' FROM DUAL UNION ALL
SELECT '123,565,321,234', '123,234' FROM DUAL

Then you can split the strings using XMLTABLE and use MINUS to find the differences between the two sets and collate the results into a collection then you can aggregate the collection:

SELECT col1,
       col2,
       ( SELECT LISTAGG( column_value, ',' ) WITHIN GROUP ( ORDER BY column_value )
         FROM   TABLE( d.new_col )
       ) AS new_col
FROM   (
  SELECT col1,
         col2,
         CAST(
           MULTISET(
             ( SELECT TO_NUMBER( column_value )
               FROM   XMLTABLE( ('"' || REPLACE( d.col1, ',', '","' ) || '"') )
               MINUS
               SELECT TO_NUMBER( column_value )
               FROM   XMLTABLE( ('"' || REPLACE( d.col2, ',', '","' ) || '"') )
             )
           ) AS SYS.ODCINUMBERLIST
         ) AS new_col
  FROM   data d
) d;

Which outputs:

COL1              | COL2    | NEW_COL  
:---------------- | :------ | :--------
123,234,565,321   | 123,234 | 321,565  
123,234,1123,2345 | 123,234 | 1123,2345
123,565,321,234   | 123,234 | 321,565  

db<>fiddle here