Find Difference between 2 columns along with difference data [message #685811] |
Thu, 31 March 2022 11:34  |
 |
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   |
 |
Michel Cadot
Messages: 68418 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:29Michel 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   |
Solomon Yakobson
Messages: 3212 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.
|
|
|
|
|
|
|