## Tuesday, September 3, 2013

### 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```