Convert an Integer to a Binary

Jim_Knicely

Although there is not a function built-in to Vertica to convert an integer into a binary, you can use the Vertica’s TO_HEX and HEX_TO_BINARY function 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/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/String/HEX_TO_BINARY.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/DataTypes/BinaryDataTypes.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/DataTypes/Numeric/INTEGER.htm

Have fun!