# convert integer to varbinary

Hello

Is it possible to convert int value directly into binary data type? For now I figured out that I can cast int to hex string and then convert it to binary like this HEX_TO_BINARY(LPAD(TO_HEX(i & 0xffffffff), 8, '0'))
You can say that there is no need for binary as all binary operators available for integer as well. And I would say that I need varbinary to combine many integer features togeather in a binary hash like this binary_feature1 || binary_feature2 || binary_feature3 || ...

So I'm wondering if there is something more efficient than TO_HEX -> LPAD -> HEX_TO_BINARY to convert int to binary

• - Select Field - Administrator

Hi Phil2 -

If you want to combine several integer based features into one variable:

Could you not do it just using bit operations on integers?

Let's say that:

• is_married is 0b1000
• has_children is 0b0100
• has_parents is 0b0010
• has_siblings is 0b0001

SQL in general does not provide the possibility of using base-2 numeric literals, unfortunately.

But I work around that by using bitwise operations.

For the constants, I create a view:

```CREATE VIEW features AS
SELECT
1<<3 AS is_married   -- 0b1000
, 1<<2 AS has_children -- 0b0100
, 1<<1 AS has_parents  -- 0b0010
, 1<<0 AS has_siblings -- 0b0001
;
```

Then, I combine the features by or-ing them together:

```  SELECT
is_married
| has_children
| has_parents
| has_siblings
AS married_child_with_children_and_siblings
,   is_married
| has_parents
| has_siblings
AS married_child_with_siblings
FROM features ;
```

And then, you can get the individual features back as Booleans like so:

```WITH example AS (
SELECT
is_married
| has_children
| has_parents
| has_siblings
AS married_child_with_children_and_siblings
,   is_married
| has_parents
| has_siblings
AS married_child_with_siblings
FROM features
)
SELECT
(married_child_with_siblings & is_married  ) <> 0 AS is_married
, (married_child_with_siblings & has_children) <> 0 AS has_children
, (married_child_with_siblings & has_parents ) <> 0 AS has_parents
, (married_child_with_siblings & has_siblings) <> 0 AS has_siblings
FROM example CROSS JOIN features;
```

All you'd have to do is to CROSS JOIN your queries with that one-row view ...

Just a suggestion ...

Marco

• edited November 2018

Hello, Marco.

I have too many features and int8 is not enough for me. Currently I need 16 bytes to store them. And there going to be more features. So I dont wont to split them

For now I wrote scalar UDF to perform int to binary cast. And it works much faster than string manipulations

• - Select Field - Administrator

@phil2 - Do you mind sharing your UDX with the community?

• Yep, I can do this

• I finally got some free time and published some of my code.

• - Select Field - Administrator

@phil2 - Thanks for sharing your code with the community!!!

• ```CREATE OR REPLACE FUNCTION int2bin (input int) RETURN VARBINARY
AS BEGIN
RETURN HEX_TO_BINARY(LPAD(TO_HEX(input & 0xffffffff), 8, '0'))::VARBINARY;
END;
```

SELECT int2bin(2);