BITAND, BITANDNOT, BITOR, BITXOR, and BITNOT
These bitwise functions operate on the "two's complement" representation of the integer value of the input arguments and return the result as a corresponding base 10 integer value in a data type based on the data type of the input arguments.
Function | Description | A bit in the two's complement representation of the result is: |
---|---|---|
BITAND | Performs a bitwise AND operation. | 1 only if the corresponding bits in both arguments are 1. |
BITANDNOT | Clears any bit in the first argument that is in the second argument. | Zero if the corresponding bit in the second argument is 1; otherwise, the result is copied from the corresponding bit in the first argument. |
BITOR | Performs a bitwise OR operation. | 1 unless the corresponding bits in both arguments are zero. |
BITXOR | Performs a bitwise exclusive OR operation. | 1 unless the corresponding bits in both arguments are the same. |
BITNOT | Performs a bitwise NOT operation. | Opposite of the corresponding bit in the argument. |
- expression
- An expression that returns a value of any built-in numeric data type. Arguments of type DECIMAL, REAL, or DOUBLE are cast to DECFLOAT. The value is truncated to a whole number.
The bit manipulation functions can operate on up to 16 bits for SMALLINT, 32 bits for INTEGER, 64 bits for BIGINT, and 113 bits for DECFLOAT. The range of supported DECFLOAT values includes integers from -2112 to 2112 -1, and special values such as NaN or INFINITY are not supported. If the two arguments have different data types, the argument supporting fewer bits is cast to a value with the data type of the argument supporting more bits. This cast impacts the bits that are set for negative values. For example, -1 as a SMALLINT value has 16 bits set to 1, which when cast to an INTEGER value has 32 bits set to 1.
The result of the functions with two arguments has the data type of the argument that is highest in the data type precedence list for promotion. If either argument is DECFLOAT, the data type of the result is DECFLOAT(34). If either argument can be null, the result can be null; if either argument is null, the result is the null value.
The result of the BITNOT function has the same data type as the input argument, except that DECIMAL, REAL, DOUBLE, or DECFLOAT(16) returns DECFLOAT(34). If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Use of the BITXOR function is recommended to toggle bits in a value. Use the BITANDNOT function to clear bits. BITANDNOT(val, pattern) operates more efficiently than BITAND(val, BITNOT(pattern)).
Example
The following examples are based on an ITEM table with a PROPERTIES column of type INTEGER.- Return all items for which the third property bit is set.
SELECT ITEMID FROM ITEM WHERE BITAND(PROPERTIES, 4) = 4
- Return all items for which the fourth or the sixth property bit
is set.
SELECT ITEMID FROM ITEM WHERE BITAND(PROPERTIES, 40) <> 0
- Clear the twelfth property of the item whose ID is 3412.
UPDATE ITEM SET PROPERTIES = BITANDNOT(PROPERTIES, 2048) WHERE ITEMID = 3412
- Set the fifth property of the item whose ID is 3412.
UPDATE ITEM SET PROPERTIES = BITOR(PROPERTIES, 16) WHERE ITEMID = 3412
- Toggle the eleventh property of the item whose ID is 3412.
UPDATE ITEM SET PROPERTIES = BITXOR(PROPERTIES, 1024) WHERE ITEMID = 3412
- Switch all the bits in a 16-bit value that has only the second
bit on.
returns -3 (with a data type of SMALLINT).VALUES BITNOT(CAST(2 AS SMALLINT))