CASE WHEN multiple partial match BigQuery

Question

I'm trying to create a new column in my BigQuery table based on the partial match values in another column. Here is an example of what I'm trying to do. Let's say this is my table:

id	statement
123	I like green
234	I love green
456	I like orange
123	I hate yellow

What I want to do is create a new table that looks like this:

id	statement	color
123	I like green	green
234	I love green	green
456	I like orange	orange
123	I hate yellow	None

I've tried using a query like this:

SELECT ID, Statement
CASE WHEN Statement IN ('green') THEN 'green' ELSE 'None' END AS color
CASE WHEN Statement IN ('orange') THEN 'orange' ELSE 'None' END AS color
FROM `data.Table1`

but this, of course, only works if it's an exact match. How can I get a new column with a value based on a partial match like described above? Thanks in advance.

Answer 1

You are close:

SELECT ID, Statement,
       (CASE WHEN Statement LIKE '%green%' THEN 'green' 
             WHEN Statement LIKE '%orange%' THEN 'orange' 
             ELSE 'None'
        END) as color
FROM `data.Table1`;

Note: This returns the first color that is matched, which is consistent with the way that you phrased the question.