title: Bigquery SQL to convert single value string to multi value based on separator/prefix
body: <p>I have a column value string with + or - orefix as below :</p>
  <pre><code>id       val
   1    +a+b+c-d-e-f+g
  </code></pre>
  <p>Now based on + or - separator I need to build the dataset as follows :</p>
  <pre><code> id new_val prefix
   1   a       +
   1   b       +
   1   c       +
   1   d       -
   1   e       -
   1   f       -
   1   g       +
  </code></pre>
  <p>And to add the string is not fixed length ie it would continue with either separator (+ or -) for different rows.
  Any guide on big-query SQL to do this transformation would be helpful.</p>
  <p>Update :</p>
  <p>I am using this query but missing some value though  :</p>
  <pre><code>with mytable as (
    select 1 as id, '+a+b+c-d-f+g' as val1, 
  )
  
  select * from (
  select id, new_val1 , '+' symbol
  from mytable, unnest(split(val1,  '+')) as new_val1 WITH OFFSET AS val1_offset
  union all
  select id, new_val1 , '-' symbol
  from mytable, unnest(split(val1,  '-')) as new_val1 WITH OFFSET AS val1_offset
  ) where length(new_val1) = 1 and new_val1 is not null
  </code></pre>