BITAND function in sql

Purpose

The BITAND function treats its inputs and its output as vectors of bits; the output is the bitwise AND of the inputs.

The types of expr1 and expr2 are NUMBER, and the result is of type NUMBER. If either argument to BITAND is NULL, the result is NULL.

The arguments must be in the range -(2^(n-1)) .. ((2^(n-1))-1). If an argument is out of this range, the result is undefined.

The result is computed in several steps. First, each argument A is replaced with the value SIGN(A)*FLOOR(ABS(A)). This conversion has the effect of truncating each argument towards zero. Next, each argument A (which must now be an integer value) is converted to an n-bit two's complement binary integer value. The two bit values are combined using a bitwise AND operation. Finally, the resulting n-bit two's complement value is converted back to NUMBER.

Notes on the BITAND Function

• The current implementation of BITAND defines n = 128.
• PL/SQL supports an overload of BITAND for which the types of the inputs and of the result are all BINARY_INTEGER and for which n = 32.

Examples

The following example performs an AND operation on the numbers 6 (binary 1,1,0) and 3 (binary 0,1,1):

SELECT BITAND(6,3) FROM DUAL;

BITAND(6,3)
-----------
          2

This is the same as the following example, which shows the binary values of 6 and 3. The BITAND function operates only on the significant digits of the binary values:

SELECT BITAND(
   BIN_TO_NUM(1,1,0),
   BIN_TO_NUM(0,1,1)) "Binary"
FROM DUAL;
 
    Binary
----------
         2