The CASE function allows you to test a number of conditional statements with arbitrary complexity. Use it when your IF functions get too long and nested.

Syntax

CASE expr
   WHEN expr_to_match THEN result
   [... more WHENs ...]
   [ELSE else_result]
END

with my_table as (
  select 'blue' as colour union all
  select 'indigo' as colour union all
  select 'blurp' as colour union all
  select 'black' as colour
)
select
  colour,
  case
    when colour = 'blue' then 'Definitely blue'
    when colour = 'indigo' then 'Kind of blue'
    when regexp_contains(colour, 'blu') then 'Maybe blue?'
    else 'Not blue'
  end as is_it_blue
from my_table

colour

blue

indigo

blurp

black

is_it_blue

Definitely blue

Kind of blue

Maybe blue?

Not blue

Truly collaborative analytics platform

Count redefines how data teams plan, build and share their analysis. It's Miro, but for data.

Discover the most flexible SQL editor

Count redefines how analysts plan, build, and share their analysis. It's Miro, but for data.

Become a SQL Expert

Get five free SQL lessons from Count's Head of Data, Taylor Brownlow.