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

Question

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

Laurenz Albe · Accepted Answer · 2021-10-11 05:35:00Z

1

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.

answered Oct 11, 2021 at 5:35

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

1 Comment

PainIsAMaster Over a year ago

thanks you very much

Tim Biegeleisen · Accepted Answer · 2021-10-11 05:38:48Z

0

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;

answered Oct 11, 2021 at 5:38

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related