CODEWITHSUNDEEP
sqlstringgoogle-bigquerycasesql-like
joen doe
joen doe

Reputation: 31

How to SELECT CASE LIKE in BigQuery

Can you assist in how to translate this pseudo code into BigQuery?

select case product_id when like 'a%' then -1 
       when product_id like 'b%' then -2 
       else product_id end as product_id
from `some.table.name`

Upvotes: 2

Views: 8047

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173003

Another option (BigQuery Standard SQL) to use "short" form of case as in below example

#standardSQL
select 
  case substr(product_id, 1, 1)
    when 'a' then '-1'
    when 'b' then '-2' 
    else product_id 
  end as product_id
from `some.table.name`

Upvotes: 2

GMB
GMB

Reputation: 222482

You are almost there. Basically, you need to use the long form of case, where conditions are repeated in each when branch; the short form that you are using is supported for equality conditions only.

Another problem is that all branches of a case expression must return the same datatype; it seems like product_id is a string, so you need to return strings rather than numbers, to be consistent with the else branch.

So:

select case 
    when product_id like 'a%' then '-1'
    when product_id like 'b%' then '-2' 
    else product_id 
end as product_id
from `some.table.name`

Upvotes: 3

Related Questions