CODEWITHSUNDEEP
sqlgoogle-bigquery
user9826192
user9826192

Reputation: 75

Bigquery CASE function not working as expected

I have a list of brand_name and need to use CASE + REGEX to match them to different brand_type.

However bigquery does not seem to be classifying the brand_name correctly as expected, i.e everything that should fall under the "Else" case gets classified to "Furniture"

here's a just a simplified query to show the logic being used:

SELECT CASE WHEN REGEXP_CONTAINS(brand_name, r"pen|scissors")= TRUE THEN 'Stationery'
WHEN REGEXP_CONTAINS(brand_name, r"chair|table")= TRUE THEN 'Furniture'
WHEN REGEXP_CONTAINS(brand_name, r"cup|pot")= TRUE THEN 'Household'
ELSE 'Stationery'
END AS brand_type FROM table

e.g expect paper to be classified as "Stationery" but it is classified as "Furniture" instead:

id brand_name brand_type
1 pen Stationery
2 chair Furniture
3 cup Household
4 paper Furniture

Upvotes: 0

Views: 556

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Use below instead

SELECT CASE 
  WHEN REGEXP_CONTAINS(brand_name, r"pen|scissors") THEN 'Stationery'
  WHEN REGEXP_CONTAINS(brand_name, r"chair|table") THEN 'Furniture'
  WHEN REGEXP_CONTAINS(brand_name, r"cup|pot") THEN 'Household'
  ELSE 'Stationery'
END AS brand_type 
FROM table

As you can see, you had extra CASE which broke the logic.
Also you do not need to use =TRUE

Upvotes: 2

Related Questions