Home » SQL & PL/SQL » SQL & PL/SQL » Find Difference between 2 columns along with difference data (Oracle 12g)
Find Difference between 2 columns along with difference data [message #685811] Thu, 31 March 2022 11:34 Go to next message
abhayman
Messages: 37
Registered: August 2011
Location: CA
Member
Hi,

I have data in 2 columns in below format

|COLA| |COLB|
|abc;mno;def| |abc;def;xyz|
|efg| |efg|
|def;abc| |abc;def|

I am trying to find if data is matching at row level or not. And if data is not matching then what is difference.

|COLA| |COLB| |MATCHED| |MISSING_A| |MISSING_B|
------------- ------------ -------------- ----------- -----------
|abc;mno;def| |abc;def;xyz| |NOT MATCHING| |xyz| |mno|
|efg| |efg| |MATCHING| || ||
|def;abc| |abc;def| |MATCHING| || ||

I am able to do basic matching but I am not sure how to get value of MISSING_A and MISSING_B

case
when COL1 is NULL and B.COL2 is NULL then 'MATCHED'
when COL1=COL2 then 'MATCHING'
else 'NOT MATCHING'
end as STATUS
Any help would be great.

Regards

**My apologies for bad formatting.

[Updated on: Thu, 31 March 2022 11:38]

Report message to a moderator

Re: Find Difference between 2 columns along with difference data [message #685813 is a reply to message #685811] Thu, 31 March 2022 14:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Sat, 24 April 2021 09:45

Michel Cadot wrote on Fri, 08 January 2021 12:29
Michel Cadot wrote on Fri, 08 January 2021 12:08

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
And many are still waiting for your feedback in your previous topics.
Re: Find Difference between 2 columns along with difference data [message #685821 is a reply to message #685813] Fri, 01 April 2022 08:57 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
with function missing(
                      p_token_list1 varchar2,
                      p_token_list2 varchar2
                     )
       return varchar2
       is
           v_work_token     varchar2(4000);
           v_missing_tokens varchar2(4000);
           v_position       int;
       begin
           v_missing_tokens := ';' || p_token_list2 || ';';
           for v_i in 1..regexp_count(p_token_list1,';') + 1 loop
             v_work_token := ';' || regexp_substr(p_token_list1,'[^;]+',1,v_i) || ';';
             v_position := instr(v_missing_tokens,v_work_token);
             if v_position > 0
               then
                 v_missing_tokens := substr(v_missing_tokens,1,v_position) ||
                                     substr(v_missing_tokens,v_position + length(v_work_token));
             end if;
           end loop;
           return trim(both ';' from v_missing_tokens);
     end;
     t as (
           select  cola,
                   colb,
                   missing(colb,cola) missing_a,
                   missing(cola,colb) missing_b
             from  sample
          )
select  cola,
        colb,
        case
          when missing_a is not null then 'NOT MATCHING'
          when missing_b is not null then 'NOT MATCHING'
          else 'MATCHING'
        end matched,
        missing_a,
        missing_b
  from  t
/

COLA        COLB        MATCHED      MISSING_A       MISSING_B
----------- ----------- ------------ --------------- ---------------
abc;mno;def abc;def;xyz NOT MATCHING mno             xyz
efg         efg         MATCHING
def;abc     abc;def     MATCHING

SQL>
SY.
Re: Find Difference between 2 columns along with difference data [message #685822 is a reply to message #685813] Fri, 01 April 2022 09:23 Go to previous messageGo to next message
piripicchio
Messages: 20
Registered: April 2018
Location: Rome
Junior Member
My attempt attached.

COLA                           COLB                           MATCHED              MISSING_A            MISSING_B           
------------------------------ ------------------------------ -------------------- -------------------- --------------------
abc;mno;def                    abc;def;xyz                    NOT MATCHING         xyz                  mno                 
efg                            efg                            MATCHED              (null)               (null)              
def;abc                        abc;def                        MATCHED              (null)               (null)              
(null)                         abc;def                        NOT MATCHING         abc;def              (null)              
(null)                         (null)                         MATCHED              (null)               (null)              
abc;def                        (null)                         NOT MATCHING         (null)               abc;def             
  • Attachment: DEMO_1.SQL
    (Size: 2.59KB, Downloaded 1207 times)
Re: Find Difference between 2 columns along with difference data [message #685823 is a reply to message #685822] Fri, 01 April 2022 13:19 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
1. OP never stated token can't contain commas.
2. DBMS_UTILITY.COMMA_TO_TABLE only works on tokens that follow Oracle naming rules.
3. Your solution doesn't take into account if token is listed more than once therefore it considers, for example,'a;b;b;a' and 'b;a' as a match (although OP needs to clarify - it might be intended behavior).

SY.

[Updated on: Fri, 01 April 2022 13:21]

Report message to a moderator

Re: Find Difference between 2 columns along with difference data [message #685825 is a reply to message #685823] Sat, 02 April 2022 12:48 Go to previous messageGo to next message
abhayman
Messages: 37
Registered: August 2011
Location: CA
Member
Thank you all for guidance and suggestion. This was really helpful in getting the issue addressed.
Re: Find Difference between 2 columns along with difference data [message #685827 is a reply to message #685823] Mon, 04 April 2022 02:04 Go to previous message
piripicchio
Messages: 20
Registered: April 2018
Location: Rome
Junior Member
Solomon Yakobson wrote on Fri, 01 April 2022 20:19

2. DBMS_UTILITY.COMMA_TO_TABLE only works on tokens that follow Oracle naming rules.
And of course you're right, I completely forgot this point. My bad.

[Updated on: Mon, 04 April 2022 02:04]

Report message to a moderator

Previous Topic: XML Generation
Next Topic: Oracle JOIN tables
Goto Forum:
  


Current Time: Thu Mar 28 15:34:09 CDT 2024