Categories:

Bitwise Expression Functions

BITNOT

Returns the bitwise negation of a numeric expression.

Aliases:

BIT_NOT

Syntax

BITNOT( <expr> )

Arguments

expr

This expression must evaluate to a data type that can be cast to INTEGER.

Returns

Returns an integer that represents the bitwise negation of the input expression.

Usage Notes

  • If the data type of the argument is numeric but not INTEGER (e.g. FLOAT, DECIMAL, etc.), then the argument is cast to INTEGER.

  • If the data type of the argument is a string (e.g. VARCHAR), then the argument is cast to INTEGER if possible. E.g. the string ‘12.3’ is cast to 12. If the value cannot be cast to INTEGER, then the value is treated as NULL.

  • If the argument is NULL, the result is NULL.

Examples

This example shows how to use BITNOT:

Create a simple table and data:

CREATE TABLE bits (ID INTEGER, bit1 INTEGER, bit2 INTEGER);
INSERT INTO bits (ID, bit1, bit2) VALUES 
    (   11,    1,     1),    -- Bits are all the same.
    (   24,    2,     4),    -- Bits are all different.
    (   42,    4,     2),    -- Bits are all different.
    ( 1624,   16,    24),    -- Bits overlap.
    (65504,    0, 65504),    -- Lots of bits (all but the low 6 bits)
    (    0, NULL,  NULL)     -- No bits
    ;

Execute the query:

SELECT bit1, bit2, BITNOT(bit1), BITNOT(bit2) FROM bits;

Output:

+------+-------+--------------+--------------+
| BIT1 |  BIT2 | BITNOT(BIT1) | BITNOT(BIT2) |
|------+-------+--------------+--------------|
|    1 |     1 |           -2 |           -2 |
|    2 |     4 |           -3 |           -5 |
|    4 |     2 |           -5 |           -3 |
|   16 |    24 |          -17 |          -25 |
|    0 | 65504 |           -1 |       -65505 |
| NULL |  NULL |         NULL |         NULL |
+------+-------+--------------+--------------+