title: splitting substrings and creating new columns for each in big query
body: <p>I am would like to split a space delimited string into 5 and create columns for each, but am finding it difficult to produce the desired output. 
  Edit: using standard SQL dialect </p>
  
  <p>sample data: </p>
  
  <pre><code>Row published_at                data_string          device id 
  1   2016-10-26T22:53:03.209Z    70.77 3.38 61.65 7.98 73.20 3.29 63.55 nan nan nan nan    2a0025000351353337353037
  ... 
  1 of 570 rows
  </code></pre>
  
  <p>desired output: </p>
  
  <pre><code>Row published_at                battery temp1  humid1 temp2  humid2 temp3 humid3 device_id   
  1   2016-11-03T16:24:09.833Z    70.77 3.38 61.65 7.98 73.20 3.29 63.55 2a0025000351353337353037 
  1 of 570 rows
  </code></pre>
  
  <p>attempted query 1.a: </p>
  
  <pre><code>WITH
    h2a0025_2 AS (
    SELECT
      TIMESTAMP '2016-10-26T22:53:03.209Z' AS published_at,
      '70.77 3.38 61.65 7.98 73.20 3.29 63.55 nan nan nan nan' AS data_string,
      '2a0025000351353337353037' AS device_id
    UNION ALL
    SELECT
      TIMESTAMP '2016-10-26T22:53:03.209Z',
      '70.77 3.38 61.65 7.98 73.20 3.29 63.55 nan nan nan nan',
      '2a0025000351353337353037' )
  SELECT
    published_at,
    parts[OFFSET(0)] AS Battery,
    parts[OFFSET(1)] AS Temp1,
    parts[OFFSET(1)] AS Humid1,
    parts[OFFSET(2)] AS Temp2,
    parts[OFFSET(3)] AS Humid2,
    parts[OFFSET(4)] AS Temp3,
    parts[OFFSET(5)] AS Humid3,
    device_id
  FROM (
    SELECT
      * EXCEPT(data_string),
      SPLIT(data_string, ' ') AS parts
    FROM
      `h2a0025_2`);
  </code></pre>
  
  <p>result 1.a: 2 identical rows</p>
  
  <pre><code>  Row   published_at                battery temp1  humid1 temp2  humid2 temp3 humid3 device_id   
      1   2016-11-03T16:24:09.833Z    70.77 3.38 61.65 7.98 73.20 3.29 63.55 2a0025000351353337353037 
      2   2016-11-03T16:24:09.833Z    70.77 3.38 61.65 7.98 73.20 3.29 63.55 2a0025000351353337353037
  2 of 2 rows
  </code></pre>
  
  <p>attempt 2:</p>
  
  <pre><code> SELECT
        published_at,
        parts[OFFSET(0)] AS Battery,
        parts[OFFSET(1)] AS Temp1,
        parts[OFFSET(1)] AS Humid1,
        parts[OFFSET(2)] AS Temp2,
        parts[OFFSET(3)] AS Humid2,
        parts[OFFSET(4)] AS Temp3,
        parts[OFFSET(5)] AS Humid3,
        device_id
      FROM (
        SELECT
          * EXCEPT(data_string),
          SPLIT(data_string, ' ') AS parts
        FROM
          `myproject.mydataset.h2a0025_2`);
  </code></pre>
  
  <p>Result: 
        Query Failed
        Error: Array index 3 is out of bounds (overflow)</p>