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

Comments

  • marcothesanemarcothesane - 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

  • phil2phil2
    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

  • Jim_KnicelyJim_Knicely - 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.
    Here we go: https://github.com/phil-88/vertica-udf/blob/master/README.md

  • Jim_KnicelyJim_Knicely - 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);

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file