Convert Hex to Binary in Google BigQuery

Question

How do convert hex value to Binary in Google BigQuery. Example 'D3' to 11001011.

Answer 1

There isn't a native function in BigQuery fot that. You can create your own function for that. The following code, for example, creates a function that converts a valid hex STRING to binary STRING:

CREATE TEMP FUNCTION hex_to_binary(x STRING) AS
(
  REPLACE(REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(x, '0','0000'), '1','0001'),'2','0010'),'3','0011'),'4','0100'),'5','0101'),'6','0110'),'7','0111'),'8','1000'),'9','1001'),'A','1010'),'B','1011'),'C','1100'),'D','1101'),'E','1110'),'F','1111')
);


SELECT hex_to_binary('1EF01D5')

This is just a hardcode example of how to do that. There is no exception treatment to check if the input is a hex STRING. You can use it as a base for improvements in order to achieve the exact function that you want. You can learn more about UDF's in BigQuery here

Answer 2

There's a way simpler solution:

SET decimal = CAST("0xff" AS INT64);

And then to binary:

bqutil.fn.to_binary(255);

And considering the question by @Pawankumar Sharma

bqutil.fn.to_binary(255);

Btw backup to hex is - so that's the third function you actually need when working with different bases. Strangly that's not really consistent:

SET hex = FORMAT("%X", 255);