bigquery hex string to bytes/int

Question

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.

after processing this:

#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

(thanks to Mikhail Berlyant)

i've got 4 columns (aiout,biout,avout,bvout) with each consisting of two bytes in a hex string looking like this:

       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                   

with functions like cast, format, integer, ... i've got always an internal error and the request could not be completed.

best regards

Answer 1

Here is an example that builds on Mikhail's answer to your previous question. It takes the space-separated bytes and turns them into a string prepended with 0x with no spaces that can be cast to an int64:

#standardSQL
CREATE TEMP FUNCTION HexToInt(hex_string STRING) AS (
  IFNULL(SAFE_CAST(CONCAT('0x', REPLACE(hex_string, ' ', '')) AS INT64), 0)
);

WITH `data.source` AS (
  SELECT 'S,0,2B3,8, C2 B3 00 00 00 00 03 DE' AS frame UNION ALL
  SELECT 'S,0,3FA,6, 00 E0 04 A5 00 0B' UNION ALL
  SELECT 'S,0,440,8, 83 40 4E A5 00 47 00 64' UNION ALL
  SELECT 'S,0,450,8, 84 50 01 12 01 19 01 B3' UNION ALL
  SELECT 'S,0,4B0,8, 84 B0 4E A5 00 43 00 64' 
)
SELECT *,
  HexToInt(Aiout) AS Aiout_int64,
  HexToInt(Biout) AS Biout_int64,
  HexToInt(Avout) AS Avout_int64,
  HexToInt(Bvout) AS Bvout_int64
FROM (
  SELECT
    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 frame, TRIM(SPLIT(frame)[OFFSET(4)]) AS bytes, SUBSTR(TRIM(SPLIT(frame)[OFFSET(4)]), 1, 2) AS f
    FROM `data.source`
    WHERE SUBSTR(TRIM(SPLIT(frame)[OFFSET(4)]), 1, 2) IN ('83', '84')
  ), UNNEST(SPLIT(bytes, ' ')) AS b WITH OFFSET AS p
  GROUP BY frame, bytes
  ORDER BY frame
);

The output looks like this:

+------------------------------------+-------------------------+-------------+-----------+-------------+-------------+-------------+-------------+-------------+-------------+
|               frame                |          bytes          |    Aiout    |   Biout   |    Avout    |    Bvout    | Aiout_int64 | Biout_int64 | Avout_int64 | Bvout_int64 |
+------------------------------------+-------------------------+-------------+-----------+-------------+-------------+-------------+-------------+-------------+-------------+
| S,0,440,8, 83 40 4E A5 00 47 00 64 | 83 40 4E A5 00 47 00 64 |      47 00  |        64 |             |             |       18176 |         100 |           0 |           0 |
| S,0,450,8, 84 50 01 12 01 19 01 B3 | 84 50 01 12 01 19 01 B3 |             |   01      |    12 01    |      19 01  |           0 |           1 |        4609 |        6401 |
| S,0,4B0,8, 84 B0 4E A5 00 43 00 64 | 84 B0 4E A5 00 43 00 64 |             |   4E      |    A5 00    |      43 00  |           0 |          78 |       42240 |       17152 |
+------------------------------------+-------------------------+-------------+-----------+-------------+-------------+-------------+-------------+-------------+-------------+

Edit: if you want to change the order in which the bytes are interpreted, you can reverse the contents before casting, e.g.:

#standardSQL
CREATE TEMP FUNCTION HexReverse(hex_string STRING) AS (
  (SELECT STRING_AGG(s, ' ' ORDER BY off DESC)
   FROM UNNEST(SPLIT(hex_string, ' ')) AS s WITH OFFSET off)
);

CREATE TEMP FUNCTION HexToInt(hex_string STRING) AS (
  IFNULL(SAFE_CAST(CONCAT('0x', REPLACE(HexReverse(hex_string), ' ', '')) AS INT64), 0)
);

WITH `data.source` AS (
  SELECT 'S,0,2B3,8, C2 B3 00 00 00 00 03 DE' AS frame UNION ALL
  SELECT 'S,0,3FA,6, 00 E0 04 A5 00 0B' UNION ALL
  SELECT 'S,0,440,8, 83 40 4E A5 00 47 00 64' UNION ALL
  SELECT 'S,0,450,8, 84 50 01 12 01 19 01 B3' UNION ALL
  SELECT 'S,0,4B0,8, 84 B0 4E A5 00 43 00 64' 
)
SELECT *,
  HexToInt(Aiout) AS Aiout_int64,
  HexToInt(Biout) AS Biout_int64,
  HexToInt(Avout) AS Avout_int64,
  HexToInt(Bvout) AS Bvout_int64
FROM (
  SELECT
    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 frame, TRIM(SPLIT(frame)[OFFSET(4)]) AS bytes, SUBSTR(TRIM(SPLIT(frame)[OFFSET(4)]), 1, 2) AS f
    FROM `data.source`
    WHERE SUBSTR(TRIM(SPLIT(frame)[OFFSET(4)]), 1, 2) IN ('83', '84')
  ), UNNEST(SPLIT(bytes, ' ')) AS b WITH OFFSET AS p
  GROUP BY frame, bytes
  ORDER BY frame
);