Oracle BITAND Function: A Simple Guide

The BITAND function in Oracle SQL is a bitwise function. It compares two numbers (or numeric columns) on a bit-by-bit basis and returns a new number as the result of a bitwise AND operation.

This function is most commonly used to check "flags" or specific settings that are stored within a single number.

What is the BITAND Function in Oracle?

The BITAND function looks at the binary representation of two numbers. A bitwise AND operation means that a bit in the result is set to 1 only if the corresponding bits in both input numbers are also 1. Otherwise, the result bit is 0.

Simple Example (6 AND 3):

• 6 in binary is ...110
• 3 in binary is ...011
• AND operation:
  • 1 AND 0 = 0
  • 1 AND 1 = 1
  • 0 AND 1 = 0
• The result is ...010, which is 2 in decimal.

BITAND Function Syntax

The syntax for BITAND is:

BITAND(number1, number2)

Let's break that down:

• number1: The first integer.
• number2: The second integer.

Both arguments must be numbers.

Oracle BITAND Function Examples

Here are two practical examples of how to use BITAND.

Example 1: Simple Bitwise AND using BITAND

This example performs the AND operation on the numbers 5 and 3.

• 5 = binary 101
• 3 = binary 011
• Result = binary 001, which is 1.

Query:

SELECT 
  BITAND(5, 3) AS "Bitwise_AND"
FROM DUAL;

Result:

Bitwise_AND
-----------
          1

Example 2: Checking for a "Flag" or Permission with BITAND

This is the most common use case. Imagine you have a user_status column where different bits represent different permissions:

• 1 = Read Access (binary 001)
• 2 = Write Access (binary 010)
• 4 = Admin Access (binary 100)

A user with status 7 (binary 111) has all three. A user with status 5 (binary 101) has Read and Admin access, but not Write.

We can use BITAND to check if a user has "Write Access" (the 2 bit).

Query:

WITH users AS (
  SELECT 'Alice' AS user_name, 5 AS user_status FROM DUAL UNION ALL
  SELECT 'Bob' AS user_name, 7 AS user_status FROM DUAL UNION ALL
  SELECT 'Charlie' AS user_name, 1 AS user_status FROM DUAL
)
SELECT
  user_name,
  user_status,
  BITAND(user_status, 2) AS "Write_Check"
FROM users;

Result:

USER_NAME  USER_STATUS  Write_Check
---------  -----------  -----------
Alice                5            0
Bob                  7            2
Charlie              1            0

• How it works: BITAND(status, 2) will return 2 if the "write" bit is on, and 0 if it's off. This makes it easy to check for a specific permission.