How to use Postgres CASE simple/short-hand syntax with multiple conditions?

I know with Postgres CASE expression, you can simplify it down to:

SELECT a,
       CASE a WHEN 1 THEN 'one'
              WHEN 2 THEN 'two'
              ELSE 'other'
       END
    FROM test;

…so you don’t have to write etc…

CASE
WHEN a = 1 THEN ''
WHEN a = 2 THEN ''
.
..
...
WHEN a = 99 or a = 100 THEN ''
ELSE '' END

But is there a way to do this on multiple conditions with a keyword like ILIKE or LIKE? i.e.

SELECT a,
       CASE a WHEN LIKE '1' or LIKE 'one'  THEN 'one'
              WHEN LIKE '2' and (LIKE 'two' or LIKE 'too') THEN 'two'
              ELSE 'other'
       END
    FROM test;

Obviously this above doesn’t work and I was trying some other variations but could not get it to work (if its possible)?

Answer

No, the short CASE syntax only works for a single condition per branch, and the comparison must be with the = operator. Use the other syntax for what you want.

Advertisement

Answer