title: BigQuery comma delimited string evaluation
body: <p>I am using BigQuery and I'm trying to parse a comma delimited string to find specific numbers within it.</p>
  
  <p>Example table as below</p>
  
  <pre><code>|--------|-----------------------------------------------------------|
  | userID | sequence                                                  |
  |--------|-----------------------------------------------------------|
  | 123abc | 1,2,3,4,5,6,7,8                                           |                                          
  |--------|-----------------------------------------------------------|
  | 456bcd | 1,2,3,4,5,6,7,8,9,10,11                                   |
  |--------|-----------------------------------------------------------|
  | 789def | 1,2,3,4                                                   |
  |--------|-----------------------------------------------------------|
  </code></pre>
  
  <p>I need to create a CASE statement where each value of the string 'sequence' is evaluated as per the following logic and the result outputted to its own column. </p>
  
  <pre><code>SELECT userID
  ,sequence
  ,CASE WHEN sequence CONTAINS '1' THEN 1 ELSE 0 END AS action1 
  ,CASE WHEN sequence CONTAINS '2' THEN 1 ELSE 0 END AS action2 
  ,CASE WHEN sequence CONTAINS '3' THEN 1 ELSE 0 END as action3
  ....
  ,CASE WHEN sequence CONTAINS '9' AND '11' THEN 1 ELSE 0 END as action10
  </code></pre>
  
  <p>This would produce the following output.</p>
  
  <pre><code>|--------|-------------------------|-------|-------|-------|---------|
  | userID | sequence                |action1|action2|action3|action10 |
  |--------|-------------------------|-------|-------|-------|---------|
  | 123abc | 1,2,3,4,5,6,7,8         |   1   |   1   |   1   |    0    |                                          
  |--------|-------------------------|-------|-------|-------|---------|
  | 456bcd | 1,2,3,4,5,6,7,8,9,10,11 |   1   |   1   |   1   |    1    |
  |--------|-------------------------|-------|-------|-------|---------|
  | 789def | 1,2                     |   1   |   1   |   0   |    0    |
  |--------|-------------------------|-------|-------|-------|---------|
  </code></pre>
  
  <p>Please not the last CASE WHEN statement is very important as I need to account for this very specific combination of string values as its own unique action.</p>
  
  <p>I believe this would be achievable in SQL Server using something like: </p>
  
  <pre><code>CASE WHEN CHARINDEX('1', 'sequence')&gt;0 THEN 1 ELSE 0 END as action1
  ,CASE WHEN CHARINDEX('2', 'sequence')&gt;0 THEN 1 ELSE 0 END as action2
  ,CASE WHEN CHARINDEX('3', 'sequence')&gt;0 THEN 1 ELSE 0 END as action3
  ...
  ,CASE WHEN CHARINDEX('9', 'sequence')&gt;0 AND CHARINDEX('11', 'sequence')&gt;0 THEN 1 ELSE 0 END as action10
  </code></pre>
  
  <p>However I cannot find an equivalent function in BigQuery that would achieve the same result and my attempts at REGEX have also fallen short.</p>
  
  <p>I'd be very grateful for some guidance here. Thanks in advance.</p>