Comparing strings in two columns of the same table

Question

I have the following data -

Code1   Code2
G01621  E04621

I want to show all codes that are in code1 but not in code2.

I wrote the following SQL, but it is returning incorrect data -

select * from codes c1
left join codes c2 
on lower(c1.code1) = lower(c2.code2)
where c2.code2 is null

Please expand your meaning for "in code1 but not in code2". Provide additional data as a sample set of 1 is not very useful. Data should include data rows that should be selected and some that should not. — Belayer
– Belayer, Commented Aug 2, 2019 at 4:13

Gordon Linoff · Accepted Answer · 2019-08-02 10:30:41Z

1

You can use not exists:

select c.code1
from codes c
where not exists (select 1
                  from codes c2
                  where lower(c2.code2) = lower(c.code1)
                 );

edited Aug 2, 2019 at 10:30

answered Aug 2, 2019 at 2:33

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aaron Over a year ago

This is returning some records that exist in both. I have even added a tolower before comparing the two values.

Jeremy Over a year ago

No, there must be some difference between the codes. It's most likely white space or unprintable characters.

Barbaros Özhan · Accepted Answer · 2019-08-02 02:51:22Z

0

use trim() function

select * from codes c1
left join codes c2 
on trim(lower(c1.code1)) = trim(lower(c2.code2))
where c2.code1 is null;

Demo

edited Aug 2, 2019 at 2:51

answered Aug 2, 2019 at 2:45

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Comments

Mustafa BYKSY · Accepted Answer · 2019-08-02 14:21:36Z

0

Use except :

select c1.code1 from codes c1
except
select c2.code2 from codes c2

answered Aug 2, 2019 at 14:21

Mustafa BYKSY

939 bronze badges

Collectives™ on Stack Overflow

Comparing strings in two columns of the same table

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related