SQL: check if multiple values in the column SQL

Question

Is it possible to check if multiple values are in the column and based on that filter out one of them using a WHERE clause?

Obviously this code won't work, but here is the logical example of what I'd like to achieve:

SELECT * 
FROM table
WHERE IF column includes ('value1', 'value2') THEN NOT IN 'value1'

example with conditions True:

column
value1
value1
value2
value2
value1
value3
value4
value4

result:

column
value2
value2
value3
value4
value4

Side note: process has to be automated as in one upload, dataset might contain value1 which should remain in place and in the next one both of them will be populated and only value2 will be valid.

you can use 'Column1' LIKE 'Column2' for comparing two columns and using '*' will return all elements in the row. — AmateurCoder
– AmateurCoder, Commented Jun 9, 2022 at 12:08
SELECT * FROM table WHERE POSITION('value1', column) > 0 AND POSITION('value2', column) > 0 What do you mean by "filter out"? — schlonzo
– schlonzo, Commented Jun 9, 2022 at 12:20
By this I mean followin scenario: let say that I have multiple examples of 5 unique values in one column. I want to check whether two distinct of them are both in the set of those 5 and if yes on of the distinct values shouldn't be in the final outp so: WHERE NOT IN value — thegreatbudyn
– thegreatbudyn, Commented Jun 9, 2022 at 12:39
It would be helpful if you would provide sample data and a sample output for what you are looking for. How can a column contain 2 values on a record? — Mike Walton
– Mike Walton, Commented Jun 9, 2022 at 12:42
I added the concept of such column into the description @MikeWalton — thegreatbudyn
– thegreatbudyn, Commented Jun 9, 2022 at 12:45

KeithL · Accepted Answer · 2022-06-09 14:30:51Z

1

If both val1 and val2 exist then exclude val1 otherwise no filter...

declare @t table (col varchar(10))

insert into @t
values
('val1'),('val1'),('val2'),('val3')

select *
from @t
where col <> case when 2 = (select count(*) from (select col from @t where col  in('val1','val2') group by col)a)
             then 'val1'
             else '' end

Results:

col
val2
val3

This is an example when both are not present

declare @t2 table (col varchar(10))

insert into @t2
values
('val1'),('val1'),('val3')

select *
from @t2
where col <> case when 2 = (select count(*) from (select col from @t2 where col  in('val1','val2') group by col)a)
             then 'val1'
             else '' end

Results:

col
val1
val1
val3

Note: the else value needs to be a value that cannot exist in the column col

Note2: This is answered using t-sql

edited Jun 9, 2022 at 14:30

answered Jun 9, 2022 at 14:01

KeithL

5,6193 gold badges23 silver badges30 bronze badges

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

2 Comments

thegreatbudyn Over a year ago

thank you! that's brilliatly elegant and works!

KeithL Over a year ago

@thegreatbudyn You're welcome... if this answered your question, then please accept answer and potentially upvote it. Thanks.

Lukasz Szozda · Accepted Answer · 2022-06-09 14:32:35Z

1

Using QUALIFY. The idea is to compare value of the column against an array generated ad-hoc with case expression to handle subsitution logic:

SELECT *
FROM tab
QUALIFY NOT ARRAY_CONTAINS(col::VARIANT, 
ARRAY_AGG(DISTINCT CASE WHEN col IN ('value1', 'value2') THEN 'value1' END) OVER());

For sample data:

CREATE OR REPLACE TABLE tab AS
SELECT $1 AS col
FROM (VALUES 
      ('value1'), ('value1'), ('value2'), 
      ('value2'), ('value1'), ('value3'),  
      ('value4'), ('value4')
)s;

Output:

A more explicit approach is using windowed COUNT_IF:

SELECT *
FROM tab
QUALIFY col NOT IN (CASE WHEN COUNT_IF(col IN ('value1', 'value2')) OVER() > 1 
                         THEN 'value1' 
                         ELSE '' 
                    END);

edited Jun 9, 2022 at 14:32

answered Jun 9, 2022 at 14:21

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Collectives™ on Stack Overflow

SQL: check if multiple values in the column SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related