SQL Select List mulitple values with case then

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.

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'))

Advertisement

Answer