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.

Table 1. Bit manipulation functions
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 -2¹¹² to 2¹¹² -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.
```
  VALUES BITNOT(CAST(2 AS SMALLINT))
```
returns -3 (with a data type of SMALLINT).