Bitwise operators

Operators

Translation for Bitwise Operators

Conversion Table

Redshift	Snowflake	Comments
& (AND)	BITAND	Fully supported by Snowflake
| (OR)	BITOR	Fully supported by Snowflake
<< (Shift Left)	BITSHIFTLEFT
>> (Shift Right)	BITSHIFTRIGHT
# (XOR)	BITXOR	Fully supported by Snowflake
~ (NOT)	BITNOT	Fully supported by Snowflake

Sample Source Patterns

Setup data

Redshift

Query

CREATE TABLE bitwise_demo (
    col1 INTEGER,
    col2 INTEGER,
    col3 INTEGER,
    col4 VARBYTE(5),
    col5 VARBYTE(7)
);

INSERT INTO bitwise_demo (col1, col2, col3, col4, col5) VALUES
-- Binary: 110, 011, 1111, 0100100001100101011011000110110001101111, 0100100001101001
(6, 3, 15, 'Hello'::VARBYTE, 'Hi'::VARBYTE),
-- Binary: 1010, 0101, 0111, 0100000101000010, 01000011
(10, 5, 7, 'AB'::VARBYTE, 'C'::VARBYTE),   
-- Binary: 11111111, 10000000, 01000000, 010000100111100101100101, 01000111011011110110111101100100010000100111100101100101
(255, 128, 64, 'Bye'::VARBYTE, 'GoodBye'::VARBYTE),
-- Edge case with small numbers and a negative number
(1, 0, -1, 'Hey'::VARBYTE, 'Ya'::VARBYTE);

Snowflake

Query

CREATE TABLE bitwise_demo (
    col1 INTEGER,
    col2 INTEGER,
    col3 INTEGER,
    col4 BINARY(5),
    col5 BINARY(7)
);

-- Binary: 110, 011, 1111, 0100100001100101011011000110110001101111, 0100100001101001
INSERT INTO bitwise_demo (col1, col2, col3, col4, col5) SELECT 6, 3, 15, TO_BINARY(HEX_ENCODE('Hello')), TO_BINARY(HEX_ENCODE('Hi'));  
-- Binary: 1010, 0101, 0111, 0100000101000010, 01000011 
INSERT INTO bitwise_demo (col1, col2, col3, col4, col5) SELECT 10, 5, 7, TO_BINARY(HEX_ENCODE('AB')), TO_BINARY(HEX_ENCODE('C'));   
-- Binary: 11111111, 10000000, 01000000, 010000100111100101100101, 01000111011011110110111101100100010000100111100101100101
INSERT INTO bitwise_demo (col1, col2, col3, col4, col5) SELECT 255, 128, 64, TO_BINARY(HEX_ENCODE('Bye')), TO_BINARY(HEX_ENCODE('GoodBye')); 
-- Edge case with small numbers and a negative number
INSERT INTO bitwise_demo (col1, col2, col3, col4, col5) SELECT 1, 0, -1, TO_BINARY(HEX_ENCODE('Hey')), TO_BINARY(HEX_ENCODE('Ya'));

Bitwise operators on integer values

Input Code:

Redshift

IN -> Redshift_01.sql

SELECT
    -- Bitwise AND
    col1 & col2 AS bitwise_and,  -- col1 AND col2

    -- Bitwise OR
    col1 | col2 AS bitwise_or,   -- col1 OR col2

    -- Left Shift
    col3 << 1 AS left_shift_col3, -- col3 shifted left by 1

    -- Right Shift
    col3 >> 1 AS right_shift_col3, -- col3 shifted right by 1

    -- XOR
    col1 # col2 AS bitwise_xor, -- col1 XOR col2

    -- NOT
    ~ col3 AS bitwise_not -- NOT col3

FROM bitwise_demo;

Results

+-------------+------------+-----------------+------------------+-------------+-------------+
| bitwise_and | bitwise_or | left_shift_col3 | right_shift_col3 | bitwise_xor | bitwise_not |
+-------------+------------+-----------------+------------------+-------------+-------------+
|2            |7           |30               |7                 |5            |-16          |
|0            |15          |14               |3                 |15           |-8           |
|128          |255         |128              |32                |127          |-65          |
|0            |1           |-2               |-1                |1            |0            |
+-------------+------------+-----------------+------------------+-------------+-------------+

Output Code:

Snowflake

OUT -> Redshift_01.sql

SELECT
        BITAND(
        -- Bitwise AND
        col1, col2) AS bitwise_and,  -- col1 AND col2
        BITOR(

        -- Bitwise OR
        col1, col2) AS bitwise_or,   -- col1 OR col2
        -- Left Shift
        --** SSC-FDM-PG0010 - RESULTS MAY VARY DUE TO THE BEHAVIOR OF SNOWFLAKE'S BITSHIFTLEFT BITWISE FUNCTION **
        BITSHIFTLEFT(
        col3, 1) AS left_shift_col3, -- col3 shifted left by 1
        -- Right Shift
        --** SSC-FDM-PG0010 - RESULTS MAY VARY DUE TO THE BEHAVIOR OF SNOWFLAKE'S BITSHIFTRIGHT BITWISE FUNCTION **
        BITSHIFTRIGHT(
        col3, 1) AS right_shift_col3, -- col3 shifted right by 1
        BITXOR(

        -- XOR
        col1, col2) AS bitwise_xor, -- col1 XOR col2
        -- NOT
        BITNOT(col3) AS bitwise_not -- NOT col3
FROM
        bitwise_demo;

Results

+-------------+------------+-----------------+------------------+-------------+-------------+
| bitwise_and | bitwise_or | left_shift_col3 | right_shift_col3 | bitwise_xor | bitwise_not |
+-------------+------------+-----------------+------------------+-------------+-------------+
|2            |7           |30               |7                 |5            |-16          |
|0            |15          |14               |3                 |15           |-8           |
|128          |255         |128              |32                |127          |-65          |
|0            |1           |-2               |-1                |1            |0            |
+-------------+------------+-----------------+------------------+-------------+-------------+

Bitwise operators on binary data

For the BITAND, BITOR and BITXOR functions the'LEFT' parameter is added to insert padding in case both binary values have different length, this is done to avoid errors when comparing the values in Snowflake.

Redshift

Query

SELECT
    -- Bitwise AND
    col4 & col5 AS bitwise_and,  -- col4 AND col5

    -- Bitwise OR
    col4 | col5 AS bitwise_or,   -- col4 OR col5

    -- XOR
    col4 # col5 AS bitwise_xor, -- col4 XOR col5

    -- NOT
    ~ col4 AS bitwise_not -- NOT col4

FROM bitwise_demo;

Result

+-----------------+-----------------+-----------------+-------------+
| bitwise_and     | bitwise_or      | bitwise_xor     | bitwise_not |
+-----------------+-----------------+-----------------+-------------+
|0x0000004869     |0x48656C6C6F     |0x48656C2406     |0xB79A939390 |
|0x0042           |0x4143           |0x4101           |0xBEBD       |
|0x00000000427965 |0x476F6F64427965 |0x476F6F64000000 |0xBD869A     |
|0x004161         |0x487D79         |0x483C18         |0xB79A86     |
+-----------------+-----------------+-----------------+-------------+

Snowflake

Query

SELECT
    BITAND(
    -- Bitwise AND
    col4, col5, 'LEFT') AS bitwise_and,  -- col4 AND col5
    BITOR(

    -- Bitwise OR
    col4, col5, 'LEFT') AS bitwise_or,   -- col4 OR col5

    -- XOR
    BITXOR(col4, col5, 'LEFT') AS bitwise_xor, -- col4 XOR col5

    -- NOT
    BITNOT(col4) AS bitwise_not -- NOT col4
    
    FROM bitwise_demo;

Result

+---------------+---------------+---------------+-------------+
| bitwise_and   | bitwise_or    | bitwise_xor   | bitwise_not |
+---------------+---------------+---------------+-------------+
|0000004869     |48656C6C6F     |48656C2406     |B79A939390   |
|0042           |4143           |4101           |BEBD         |
|00000000427965 |476F6F64427965 |476F6F64000000 |BD869A       |
|004161         |487D79         |483C18         |B79A86       |
+---------------+---------------+---------------+-------------+

Known Issues

No issues were found.

Related EWIs

• SSC-FDM-PG0010: Results may vary due to the behavior of Snowflake's bitwise function.