SQL Select List mulitple values with case then

Question

I have a variable and based on the variable I want to exclude the value from the select command. It works fine if I want to exclude 1 string, but if it's not working if I'm excluding multiple string in same condition. Here's the code. It works fine when variable is abc and EFG, but for XYZ I want to exclude both 'abc' and 'efg'
select table.column1
from table
where column1 not like
        case 
            when variable = 'abc' THEN '%abc%'
            when variable  = 'EFG' THEN '%efg%'
            when variable  = 'XYZ' THEN '%abc%' and '%efg%'
        else 
              '_'
        END

I tried with '%abc%' and '%efg%' and '%(abc|efg)%' and '%abc%' || and || '%efg%', but none of them seems to be working.

Gordon Linoff · Accepted Answer

I think you want:
where (variable = 'abc' and column1 not like '%abc%') or
      (variable = 'EFG' and column1 not like '%EFG%') or
      (variable = 'XYZ' and column1 not like '%abc%' and column1 not like '%XYZ%') or
      (variable not in ('abc', 'EFG', 'XYZ'))

Rahid Zeynalov · Answer

I think solution of your problem is not by case when statement but sample where . I created script for that in livesql, and also share bellow:
create table tab( 
id_1 number, 
column_1 varchar2(20), 
variable_1 varchar2(20));

insert into tab(id_1,column_1,variable_1) values(8,'13 efg iu','abc');
insert into tab(id_1,column_1,variable_1) values(7,'1 3','KDG');
insert into tab(id_1,column_1,variable_1) values(6,'_','KDG');
insert into tab(id_1,column_1,variable_1) values(5,'_','XYZ');
insert into tab(id_1,column_1,variable_1) values(4,'abc 3 8','XYZ');
insert into tab(id_1,column_1,variable_1) values(3,'efg SDK','EFG');
insert into tab(id_1,column_1,variable_1) values(2,'EFGSSDK','EFG');
insert into tab(id_1,column_1,variable_1) values(1,'12abc12','abc');

commit;

select tab.id_1, tab.column_1 
from tab 
where (variable_1 = 'abc' and column_1 not like '%abc%') or 
      (variable_1 = 'EFG' and column_1 not like '%efg%') or 
      (variable_1 = 'XYZ' and column_1 not like '%abc%' and column_1 not like '%efg%') or 
      (variable_1 not in ('abc', 'EFG', 'XYZ') and column_1  not like '_');

SQL Select List mulitple values with case then

2 Answers

Add your own answers!

Ask a Question