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

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

CodePudding user response：

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.

CodePudding user response：

You could use Postgres' regex operator here:

SELECT a,
       CASE WHEN a ~ '^(1|one)$' THEN 'one'
            WHEN a ~ '^(2|two)$' THEN 'two'
            ELSE 'other'
      END
FROM test;