title: REGEX join in SQL BigQuery
body: <p>I'm setting up a dataset where I use a master table which has the name of the sales person, the id and name of the chain of stores and  the name of each brand they have, like this sample data:</p>
  <pre><code>salesperson  chain_id  chain_name        brand_name
  pepito          28      chairs4all          sitnice
  jaimito         34      eatdrinkeat         Drink &amp; eat
  pepito          28      chairs4all          AwwwChairs!
  susanita        56      something           Anything
  jaimito         567     last                 One
  </code></pre>
  <p>In another table I have a column with each store name on it and I want to append each store to its brand . But store name may have  the location appended or other information, like this sample data from the other table:</p>
  <pre><code>store_name
  sitnice
  AwwwChairs! Seville
  One 34
  SitNice nº12
  Drink &amp; eat North road 23
  </code></pre>
  <p>I want the final table like this:</p>
  <pre><code>salesperson  chain_id  chain_name        brand_name        store_name
  pepito          28      chairs4all          sitnice          sitnice
  jaimito         34      eatdrinkeat         Drink &amp; eat   Drink &amp; eat North     
  pepito          28      chairs4all          AwwwChairs!   AwwwChairs! Seville    
  susanita        56      something           Anything          null
  jaimito         567     last                 One               one 34
  pepito          28      chairs4all          sitnice          SitNice nº12
  </code></pre>
  <p>So I think I must do a join of the two tables of brand_name and store name. I tried this:</p>
  <pre><code>    SELECT brand_name, salesperson, chain_name, chain_id, store_name   
  FROM data
      cross join
       data2
      where
      brand_name like CONCAT('%', store_name,'%')
  </code></pre>
  <p>This did not return the expected result. It returned way less records than expected and only matched some of the expected records. I also tried REGEXP_CONTAINS and forced another joins, but as I had no other filed to join on, it worked really poorly.
  Thanks</p>