Oracle BIN_TO_NUM Function: A Simple Guide

The BIN_TO_NUM function in Oracle SQL is a mathematical function that converts a binary "bit vector" (represented as a list of 0s and 1s) into its equivalent decimal number.

This function is useful in data warehousing when you need to store multiple "on/off" or "yes/no" flags in a single number column. BIN_TO_NUM helps you create that number.

What is the BIN_TO_NUM Function in Oracle?

The BIN_TO_NUM(bit1, bit2, bit3, ...) function takes a list of arguments that must be either 0 or 1 and treats them as a binary number. It then returns the decimal equivalent.

The function reads the bits from left to right, with the leftmost bit being the most significant.

For example, BIN_TO_NUM(1, 0, 1) is treated as the binary number 101.

• 1 in the "4s" place (2^2)
• 0 in the "2s" place (2^1)
• 1 in the "1s" place (2^0)
• Result: 4 + 0 + 1 = 5

BIN_TO_NUM Function Syntax

The syntax for BIN_TO_NUM is as follows:

BIN_TO_NUM(expr1, expr2, ...)

Let's break that down:

• expr1, expr2, ...: A comma-separated list of expressions. Each expression must evaluate to either a 0 or a 1.

Oracle BIN_TO_NUM Function Examples

Here are two practical examples of how to use BIN_TO_NUM.

Example 1: Converting a Simple Binary Value with BIN_TO_NUM

This example converts the binary value 1101 (which is 8 + 4 + 0 + 1) into its decimal number.

Query:

SELECT 
  BIN_TO_NUM(1, 1, 0, 1) AS "Decimal_Value"
FROM DUAL;

Result:

Decimal_Value
-------------
           13

Example 2: Storing Multiple Flags in One Number using BIN_TO_NUM

This is the most common use case. Imagine you want to store three settings for a user in a single user_prefs column:

• is_admin (bit 1)
• is_editor (bit 2)
• gets_newsletter (bit 3)

Let's find the number for a user who is an admin (1), not an editor (0), and gets the newsletter (1). We want to store the binary value 101.

Query:

-- This simulates setting the flags
-- 1 (is_admin), 0 (is_editor), 1 (gets_newsletter)
SELECT 
  BIN_TO_NUM(1, 0, 1) AS "User_Pref_Value"
FROM DUAL;

Result:

User_Pref_Value
---------------
              5

You could then store the number 5 in the user_prefs column. Later, you could use the BITAND function to check these individual flags.