BITAND function in Oracle/PLSQL

BITAND

Definition:

In Oracle/PLSQL the BITAND function is used to determine whether a particular bit is set or not. It is most commonly used with the DECODE function. The function takes 2 arguments and performs these steps:

• Converts the 2 arguments to binary (n-bit two's complement binary integer value)
• Performs a standard bitwise AND operation on the two strings
• Converts the binary result back to a decimal format

The BITAND function essentially does a logical AND of two bit strings. If the values in any position are both '1', then the result will have a '1' in that position, otherwise the result will have a '0' in that position. This is a very fast, very efficient way of checking if a particular bit has been set. It's efficient because it makes use of a technique called 'bit masking'.

Syntax:

bitand( expr1, expr2 )

BITAND computes an AND operation on the bits of expr1 and expr2, both of which must resolve to non-negative integers. BITAND returns an integer value.

NOTE: The BITAND function does not determine the datatype of the value returned. Therefore, in SQL*Plus, you must specify BITAND in a wrapper, such as TO_NUMBER, which returns a datatype.

Applies To

·         Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

For Example

Bitand(5,3)          would return 1

Bitand(15,7)       would return 7

Bitand(5,2)          would return 0

Bitand(5,0)          would return 0

Bitand(6,2)          would return 2

Examples:-

1. BITAND(1,1)  — returns 1
2. BITAND(1,0)  — returns 0
3. BITAND(0,1)  — returns 0
4. BITAND(-1,-1)– returns -1
5.  BITAND(null,null) – returns NULL

6. BITAND(24,18) – returns 8

Explanation:-

Binary representation of 24 is 11000
Binary representation of 15 is 1111

24	15	BITAND	Result	Explanation
1
1	1	====>	1	(1 AND 1) is 1
0	1	====>	0	(0 AND 1) is 0
0	1	====>	0	(0 AND 1) is 0
0	1	====>	0	(0 AND 1) is 0

(Note that that bits are considered from right, least significant first)

Result is 1000 in binary format. If you convert 1000 binary into decimal format we get 8.


So BITAND(24,18) = 8

7. BITAND(6,2) - returns 2

Binary representation of 6 is 110
Binary representation of 2 is  10


BITAND returns 10 in Binary, which is 2

8. BITAND(6,3) – returns 2

Binary representation of 6 is 110
Binary representation of 2 is  11


BITAND returns 10 in Binary, which is 2