CODEWITHSUNDEEP
google-bigquery
Elliott Brossard
Elliott Brossard

Reputation: 33755

How can I convert between INT64 and binary STRING using BigQuery?

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?

Upvotes: 1

Views: 6402

Answers (3)

Sergey Geron
Sergey Geron

Reputation: 10212

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

Upvotes: 0

ropsnou
ropsnou

Reputation: 174

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 ...

Upvotes: 1

Elliott Brossard
Elliott Brossard

Reputation: 33755

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.)

Upvotes: 16

Related Questions