title: How to search for rows containing specific words then return count of each word?
body: <p>I have 150,000 rows of data which I'm attempting to query in Google BigQuery.</p>
  
  <p>Column <code>Text</code> contains various lengths of text, from which I want to query for particular keywords.</p>
  
  <p>I've gotten as far as the query below which returns all rows containing a particular keyword (e.g. facebook):</p>
  
  <pre><code>SELECT Text From Data.Set_1 
  WHERE Text CONTAINS 'facebook'
  </code></pre>
  
  <p><strong>Questions:</strong></p>
  
  <p>1) How do I improve the query so that it returns a total count of all occurrences of the keyword 'facebook' across 'Text' in a new column?</p>
  
  <p>2) How do I upscale this to multiple keywords (facebook, cnn, bbc, twitter) and return a total count of each keyword present in the data (eg facebook 42, cnn 54, bbc 88, twitter 49)?</p>