Convert an Integer to a Binary

Posted March 11, 2019 by Jim Knicely, Vertica Principal Solution Architect

Although there is not a built-in function in Vertica to convert an integer to a binary, you can use the Vertica TO_HEX and HEX_TO_BINARY functions to create your own!

Example: dbadmin=> SELECT HEX_TO_BINARY(LPAD(TO_HEX(2 & 0xffffffff), 8, '0'))::VARBINARY INT_TO_BINARY; INT_TO_BINARY ------------------ \000\000\000\002 (1 row) dbadmin=> CREATE OR REPLACE FUNCTION int2bin (input INT) RETURN VARBINARY dbadmin-> AS BEGIN dbadmin-> RETURN HEX_TO_BINARY(LPAD(TO_HEX(input & 0xffffffff), 8, '0'))::VARBINARY; dbadmin-> dbadmin-> END; CREATE FUNCTION dbadmin=> SELECT int2bin(2); int2bin ------------------ \000\000\000\002 (1 row) Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/String/HEX_TO_BINARY.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/DataTypes/BinaryDataTypes.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/DataTypes/Numeric/INTEGER.htm