There is a feature request on the BigQuery issue tracker for a conversion function, such as between INT64 and binary STRING. Is there a workaround in the meantime aside from using a JavaScript UDF?
Add a comment
|
3 Answers
Yes, you can implement conversion between INT64 and binary STRING using SQL without needing JavaScript. Here is an example:
CREATE TEMP FUNCTION ToBinary(x INT64) AS (
(SELECT STRING_AGG(CAST(x >> bit & 0x1 AS STRING), '' ORDER BY bit DESC)
FROM UNNEST(GENERATE_ARRAY(0, 63)) AS bit)
);
CREATE TEMP FUNCTION FromBinary(s STRING) AS (
(SELECT SUM(CAST(c AS INT64) << (LENGTH(s) - 1 - bit))
FROM UNNEST(SPLIT(s, '')) AS c WITH OFFSET bit)
);
SELECT x, ToBinary(x) AS binary, FromBinary(ToBinary(x)) AS roundtrip
FROM UNNEST([1, 123456, 9876543210, -1001]) AS x;
This returns:
+------------+------------------------------------------------------------------+------------+
| x | binary | roundtrip |
+------------+------------------------------------------------------------------+------------+
| 1 | 0000000000000000000000000000000000000000000000000000000000000001 | 1 |
| 123456 | 0000000000000000000000000000000000000000000000011110001001000000 | 123456 |
| 9876543210 | 0000000000000000000000000000001001001100101100000001011011101010 | 9876543210 |
| -1001 | 1111111111111111111111111111111111111111111111111111110000010111 | -1001 |
+------------+------------------------------------------------------------------+------------+
As a bonus, here is a way to convert between INT64 and hexadecimal STRING:
CREATE TEMP FUNCTION ToHex(x INT64) AS (
(SELECT STRING_AGG(FORMAT('%02x', x >> (byte * 8) & 0xff), '' ORDER BY byte DESC)
FROM UNNEST(GENERATE_ARRAY(0, 7)) AS byte)
);
CREATE TEMP FUNCTION FromHex(s STRING) AS (
(SELECT SUM(CAST(CONCAT('0x', SUBSTR(s, byte * 2 + 1, 2)) AS INT64) << ((LENGTH(s) - (byte + 1) * 2) * 4))
FROM UNNEST(GENERATE_ARRAY(1, LENGTH(s) / 2)) WITH OFFSET byte)
);
SELECT x, ToHex(x) AS hex, FromHex(ToHex(x)) AS roundtrip
FROM UNNEST([1, 123456, 9876543210, -1001]) AS x;
This returns:
+------------+------------------+------------+
| x | hex | roundtrip |
+------------+------------------+------------+
| 1 | 0000000000000001 | 1 |
| 123456 | 000000000001e240 | 123456 |
| 9876543210 | 000000024cb016ea | 9876543210 |
| -1001 | fffffffffffffc17 | -1001 |
+------------+------------------+------------+
(Octal conversion is left as an exercise to the reader.)
1 Comment
Federico Panunzio
Great that you found the workaround! You can accept your answer 48 hours after posting it
It's also possible to use CAST and FORMAT:
Int to hex:
SELECT FORMAT("%X", my_int) AS my_hex ...
HEX to int
SELECT CAST(my_hex AS INT64) AS my_int ...
Comments
Update 2021:
from_binary and to_binary functions have been integrated into official bqutil public project.
Convert binary representation to integer:
SELECT bqutil.fn.from_binary('111')
7
Convert integer to binary representation:
SELECT bqutil.fn.to_binary(7)
0000000000000000000000000000000000000000000000000000000000000111
