title: bigquery hex string to bytes/int
body: <p>is there a possibility to convert a hex string consisting of two bytes to bytes/int whereas the first byte is the lsb and the second is the msb.</p>
  
  <p>after processing this:</p>
  
  <pre><code>#standardSQL
  SELECT
    timestamp, CAN_Frame, bytes,
    STRING_AGG(CASE WHEN f='83' AND p IN (5, 6) THEN b ELSE '' END, ' ' ORDER BY p) AS Aiout,
    STRING_AGG(CASE WHEN (f='83' AND p=7) OR (f='84' AND p=2)  THEN b ELSE '' END, ' ' ORDER BY p) AS Biout, 
    STRING_AGG(CASE WHEN f='84' AND p IN (3, 4) THEN b ELSE '' END, ' ' ORDER BY p) AS Avout,
    STRING_AGG(CASE WHEN f='84' AND p IN (5, 6) THEN b ELSE '' END, ' ' ORDER BY p) AS Bvout
  FROM (
    SELECT timestamp, CAN_Frame, TRIM(SPLIT(CAN_Frame)[OFFSET(4)]) AS bytes, 
  SUBSTR(TRIM(SPLIT(CAN_Frame)[OFFSET(4)]), 1, 2) AS f 
    FROM `data.source`
    WHERE SUBSTR(TRIM(SPLIT(CAN_Frame)[OFFSET(4)]), 1, 2) IN ('83', '84')
  ), UNNEST(SPLIT(bytes, ' ')) AS b WITH OFFSET AS p
  GROUP BY timestamp,CAN_Frame, bytes 
  ORDER BY timestamp desc
  </code></pre>
  
  <p>(thanks to Mikhail Berlyant) </p>
  
  <p>i've got 4 columns (aiout,biout,avout,bvout) with each consisting of two bytes in a hex string looking like this:</p>
  
  <pre><code>       Aiout    Biout  Avout    Bvout
                   00    25 00    21 00 
         03 00      0D                   
                    00   27 00    08 00 
                   00    1C 00    15 00 
                   00    25 00    21 00 
         03 00      03                   
                    00   2A 00    26 00 
         03 00      0D                   
         00 00      07                   
         04 00      04                   
  </code></pre>
  
  <p>with functions like cast, format, integer, ... i've got always an internal error and the request could not be completed.</p>
  
  <p>best regards </p>