title: CASE WHEN multiple partial match BigQuery
body: <p>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:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">id</th>
  <th style="text-align: center;">statement</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">123</td>
  <td style="text-align: center;">I like green</td>
  </tr>
  <tr>
  <td style="text-align: left;">234</td>
  <td style="text-align: center;">I love green</td>
  </tr>
  <tr>
  <td style="text-align: left;">456</td>
  <td style="text-align: center;">I like orange</td>
  </tr>
  <tr>
  <td style="text-align: left;">123</td>
  <td style="text-align: center;">I hate yellow</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>What I want to do is create a new table that looks like this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">id</th>
  <th style="text-align: center;">statement</th>
  <th style="text-align: right;">color</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">123</td>
  <td style="text-align: center;">I like green</td>
  <td style="text-align: right;">green</td>
  </tr>
  <tr>
  <td style="text-align: left;">234</td>
  <td style="text-align: center;">I love green</td>
  <td style="text-align: right;">green</td>
  </tr>
  <tr>
  <td style="text-align: left;">456</td>
  <td style="text-align: center;">I like orange</td>
  <td style="text-align: right;">orange</td>
  </tr>
  <tr>
  <td style="text-align: left;">123</td>
  <td style="text-align: center;">I hate yellow</td>
  <td style="text-align: right;">None</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I've tried using a query like this:</p>
  <pre><code>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`
  </code></pre>
  <p>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.</p>