How to return the difference between 2 strings using Oracle SQL only

Question

I have 2 strings for example:

• 'Source:Siebel; Name:Mary Jane; Gender:F; Age:24; N;'
• 'Source:Siebel; Name:Marie; Gender:F; Age:24; N;'

The result I need is:

• Name:Mary Jane;
• Name:Marie;

Most likely I need to reverse below code

with cte1 as  (
    select 1 id, 'Source:Siebel; Name:Mary Jane; Gender:F; Age:24; N;' str from dual
    union all
    select 2 id, 'Source:Siebel; Name:Marie; Gender:F; Age:24; N;' str from dual
), cte2 as (
    SELECT distinct id, trim(regexp_substr(str, '[^ ]+', 1, level)) str
    FROM cte1 t
    CONNECT BY instr(str, ' ', 1, level - 1) > 0
)
select distinct t1.str
from cte2 t1
join cte2 t2 on (t1.str = t2.str and t1.id != t2.id)

from Oracle Function to return similarity between strings

as the result was the similarities [QueryResult] of the 2 strings

I cannot use the procedure as I need this SQL script to run in Oracle Fusion

Answer 1

Would this help?

SQL> with cte1 as  (
  2   select 1 id, 'Source:Siebel; Name:Mary Jane; Gender:F; Age:24; N;' str from dual
  3   union all
  4   select 2 id, 'Source:Siebel; Name:Marie; Gender:F; Age:24; N;' str from dual
  5   ),
  6  cte2 as
  7    (select id,
  8       column_value lvl,
  9       trim(regexp_substr(str, '[^;]+', 1, column_value)) str
 10     from cte1 cross join
 11       table(cast(multiset(select level from dual
 12                           connect by level <= regexp_count(str, ';') +1
 13                          ) as sys.odcinumberlist))
 14    )
 15  select a.str, b.str
 16  From cte2 a join cte2 b on a.id < b.id and a.lvl = b.lvl and a.str <> b.str;

STR             STR
--------------- ---------------
Name:Mary Jane  Name:Marie

SQL>