How Convert binary number to decimal and vice versa

In Oracle, BIN_TO_NUM is a built-in bitwise utility function introduced in Oracle 11g (for bitmap operations). It is used to convert a series of binary flags (0s and 1s) into a single numeric value — basically converting a bit vector into its decimal equivalent.

---

✅ Syntax:

BIN_TO_NUM(bit1, bit2, ..., bitn)

• Each bit must be 0 or 1
• The function returns a number which is the decimal equivalent of the binary input.

---

📌 How It Works:

Each bit represents a power of 2:

BIN_TO_NUM(b3, b2, b1, b0) = b3*8 + b2*4 + b1*2 + b0*1

---

🔍 Example 1: Simple Usage

SELECT BIN_TO_NUM(1, 0, 1, 1) AS result FROM dual;

• Binary = 1011
• Decimal = 1×8 + 0×4 + 1×2 + 1×1 = 11

Output:

RESULT
------
11

---

🔍 Example 2: Represent Feature Flags

Imagine 4 system features: A, B, C, D
Each flag is either 0 (off) or 1 (on):

SELECT
  feature_a,
  feature_b,
  feature_c,
  feature_d,
  BIN_TO_NUM(feature_a, feature_b, feature_c, feature_d) AS flag_number
FROM (
  SELECT 1 AS feature_a, 0 AS feature_b, 1 AS feature_c, 1 AS feature_d FROM dual
);

This returns the bitmask number 11 as above.

---

🛠️ Use Cases:

• Converting bit flags to a number (permissions, feature toggles).
• Storing bitwise combinations efficiently.
• Reverse of NUMTODSINTERVAL (in some bit flag logic scenarios).

---

🔄 To Convert Back: Use MOD and Bitwise Math or Functions

Here's how you can convert a number back to binary flags in Oracle — essentially the reverse of BIN_TO_NUM.

---

🔄 Reverse: Convert Number to Binary Flags in Oracle

Oracle doesn't have a built-in function like NUM_TO_BIN, but you can easily derive it using arithmetic and PL/SQL or SQL logic.

---

✅ Example: Decompose a Number into 4 Binary Bits

SELECT 
  num,
  FLOOR(MOD(num / 8, 2)) AS bit_4, -- 2^3
  FLOOR(MOD(num / 4, 2)) AS bit_3, -- 2^2
  FLOOR(MOD(num / 2, 2)) AS bit_2, -- 2^1
  MOD(num, 2)             AS bit_1  -- 2^0
FROM (
  SELECT 11 AS num FROM dual
);

Result:

NUM | BIT_4 | BIT_3 | BIT_2 | BIT_1
----|-------|-------|-------|------
 11 |   1   |   0   |   1   |   1

Which represents the binary: 1011

---

🔁 Reusable Function: Convert a number to N-bit binary string

CREATE OR REPLACE FUNCTION num_to_bin(p_num IN NUMBER, p_bits IN NUMBER)
  RETURN VARCHAR2
IS
  v_result VARCHAR2(1000) := '';
BEGIN
  FOR i IN REVERSE 0..(p_bits - 1) LOOP
    v_result := v_result || TRUNC(MOD(p_num / POWER(2, i), 2));
  END LOOP;
  RETURN v_result;
END;
/

✅ Usage:

SELECT num_to_bin(11, 4) AS binary FROM dual;

Output:

BINARY
-------
1011

---

Here's a version that returns individual bit columns for a number — so you can use them in queries, reports, or condition checks.

---

✅ SQL Query to Extract Individual Bits (Up to N bits)

Let’s assume you want to extract 8 bits (bit_8 to bit_1) from a number.

SELECT 
  num,
  FLOOR(MOD(num / POWER(2, 7), 2)) AS bit_8,  -- 2^7
  FLOOR(MOD(num / POWER(2, 6), 2)) AS bit_7,  -- 2^6
  FLOOR(MOD(num / POWER(2, 5), 2)) AS bit_6,
  FLOOR(MOD(num / POWER(2, 4), 2)) AS bit_5,
  FLOOR(MOD(num / POWER(2, 3), 2)) AS bit_4,
  FLOOR(MOD(num / POWER(2, 2), 2)) AS bit_3,
  FLOOR(MOD(num / POWER(2, 1), 2)) AS bit_2,
  FLOOR(MOD(num / POWER(2, 0), 2)) AS bit_1
FROM (
  SELECT 173 AS num FROM dual
);

🔍 Example:

If num = 173, its binary is 10101101

Output:

NUM | B8 | B7 | B6 | B5 | B4 | B3 | B2 | B1
----|----|----|----|----|----|----|----|----
173 |  1 |  0 |  1 |  0 |  1 |  1 |  0 |  1

---

🛠️ Reusable PL/SQL Procedure to Split Into Columns

You can also create a view or stored function to dynamically return columns, but that depends on your actual use case (reporting, filtering, or flag checks).

Below is a complete SQL query that takes a number and extracts all 32 individual binary bits (bit_32 to bit_1) — useful for analyzing flags, permissions, or bitwise logic in Oracle.

---

✅ SQL: Extract 32 Bits from a Number

SELECT 
  num,
  FLOOR(MOD(num / POWER(2, 31), 2)) AS bit_32,
  FLOOR(MOD(num / POWER(2, 30), 2)) AS bit_31,
  FLOOR(MOD(num / POWER(2, 29), 2)) AS bit_30,
  FLOOR(MOD(num / POWER(2, 28), 2)) AS bit_29,
  FLOOR(MOD(num / POWER(2, 27), 2)) AS bit_28,
  FLOOR(MOD(num / POWER(2, 26), 2)) AS bit_27,
  FLOOR(MOD(num / POWER(2, 25), 2)) AS bit_26,
  FLOOR(MOD(num / POWER(2, 24), 2)) AS bit_25,
  FLOOR(MOD(num / POWER(2, 23), 2)) AS bit_24,
  FLOOR(MOD(num / POWER(2, 22), 2)) AS bit_23,
  FLOOR(MOD(num / POWER(2, 21), 2)) AS bit_22,
  FLOOR(MOD(num / POWER(2, 20), 2)) AS bit_21,
  FLOOR(MOD(num / POWER(2, 19), 2)) AS bit_20,
  FLOOR(MOD(num / POWER(2, 18), 2)) AS bit_19,
  FLOOR(MOD(num / POWER(2, 17), 2)) AS bit_18,
  FLOOR(MOD(num / POWER(2, 16), 2)) AS bit_17,
  FLOOR(MOD(num / POWER(2, 15), 2)) AS bit_16,
  FLOOR(MOD(num / POWER(2, 14), 2)) AS bit_15,
  FLOOR(MOD(num / POWER(2, 13), 2)) AS bit_14,
  FLOOR(MOD(num / POWER(2, 12), 2)) AS bit_13,
  FLOOR(MOD(num / POWER(2, 11), 2)) AS bit_12,
  FLOOR(MOD(num / POWER(2, 10), 2)) AS bit_11,
  FLOOR(MOD(num / POWER(2, 9), 2))  AS bit_10,
  FLOOR(MOD(num / POWER(2, 8), 2))  AS bit_9,
  FLOOR(MOD(num / POWER(2, 7), 2))  AS bit_8,
  FLOOR(MOD(num / POWER(2, 6), 2))  AS bit_7,
  FLOOR(MOD(num / POWER(2, 5), 2))  AS bit_6,
  FLOOR(MOD(num / POWER(2, 4), 2))  AS bit_5,
  FLOOR(MOD(num / POWER(2, 3), 2))  AS bit_4,
  FLOOR(MOD(num / POWER(2, 2), 2))  AS bit_3,
  FLOOR(MOD(num / POWER(2, 1), 2))  AS bit_2,
  FLOOR(MOD(num / POWER(2, 0), 2))  AS bit_1
FROM (
  SELECT 4294967295 AS num FROM dual
);

---

🔍 Example Output (for 4294967295 = 2^32 - 1):

All bits are set to 1:

BIT_32 | BIT_31 | ... | BIT_1
-----------------------------
   1   |   1    | ... |  1

You can replace 4294967295 with any number up to 2^32 - 1 (unsigned 32-bit integer).

---

💡 Optional: Wrap as a View

If you'd like to reuse it:

CREATE OR REPLACE VIEW binary_bits_32 AS
SELECT ... -- same query above
FROM your_table;

Or if you'd prefer a PL/SQL pipelined function for even cleaner access

Here's a clean and efficient way to create a pipelined table function in Oracle that takes a number (up to 32-bit) and returns a table of 32 rows, each showing:

• Bit position (1 to 32)

• Bit value (0 or 1)

---

✅ Step 1: Create the Return Object and Table Type

CREATE OR REPLACE TYPE bit_record AS OBJECT (
  bit_position NUMBER,
  bit_value    NUMBER
);
/

CREATE OR REPLACE TYPE bit_table AS TABLE OF bit_record;
/

---

✅ Step 2: Create the Pipelined Function

CREATE OR REPLACE FUNCTION get_binary_bits(p_num IN NUMBER)
  RETURN bit_table PIPELINED
IS
BEGIN
  FOR i IN REVERSE 0..31 LOOP
    PIPE ROW(bit_record(32 - i, FLOOR(MOD(p_num / POWER(2, i), 2))));
  END LOOP;
  RETURN;
END;
/

---

✅ Step 3: Query the Function Like a Table

SELECT * FROM TABLE(get_binary_bits(173));

Output (173 = 00000000 00000000 00000000 10101101):

BIT_POSITION | BIT_VALUE
-------------|-----------
1            | 0
2            | 0
...          | ...
25           | 1
26           | 0
27           | 1
28           | 0
29           | 1
30           | 1
31           | 0
32           | 1

---

💡 Use Cases:

• Flag decoding
• Bitwise permission checks
• Debugging status registers or masks

• Easy to join with reference tables (bit_position meaning)