Interestingly enough, Oracle does not have a built-in function to convert Decimal numbers (i.e. integers) into Binary. This post offers an elegant way of doing so.

The following script is intended to be executed in SQL*Plus, so it uses some SQL*Plus commands:

column bin format a40 undefine N SELECT LISTAGG(SIGN(BITAND(&&N, POWER(2,LEVEL-1))),'') WITHIN GROUP(ORDER BY LEVEL DESC) bin FROM dual CONNECT BY POWER(2, LEVEL-1)<=&&N;

Result (for N=400):

BIN ------------- 110010000

Result (for N=1401):

BIN ------------ 10101111001

**Explanation:**

How many digits may the resulting binary string have? The answer comes from Math: not more than LOG(2, N) + 1. Let’s first generate a numeric range from 1 to LOG(2,N)+1:

SELECT LEVEL FROM dual CONNECT BY LEVEL<=LOG(2,&N)+1

Result (for N=1401):

LEVEL ------ 1 2 3 4 5 6 7 8 9 10 11

Alternatively, we can use mathematically equivalent condition in the CONNECT BY clause using POWER instead of LOG function:

SELECT LEVEL FROM dual CONNECT BY POWER(2,LEVEL)<=&N*2

or

SELECT LEVEL FROM dual CONNECT BY POWER(2,LEVEL-1)<=&N

Now, we will check every bit of the desired result (i.e. binary representation of N) by using BITAND function:

SELECT LEVEL, BITAND(&&N, POWER(2,LEVEL-1)) bit FROM dual CONNECT BY POWER(2,LEVEL-1)<=&&N

Result (for N=12):

LEVEL BIT ----- ---------- 1 0 2 0 3 4 4 8

Positive values in the bit column refer to a bit 1 in the corresponding position (in reverse order) of the binary value. It’s easy to turn those values to 1 by using SIGN function:

SELECT LEVEL, SIGN(BITAND(&&N, POWER(2,LEVEL-1))) bit FROM dual CONNECT BY POWER(2,LEVEL-1)<=&&N

Result (for N=12):

LEVEL BIT ----- ---------- 1 0 2 0 3 1 4 1

Here, we can see that we need to concatenate the values in the bit column in reverse order. This is very easy to do using LISTAGG function:

SELECT LISTAGG(SIGN(BITAND(&&N, POWER(2,LEVEL-1))),'') WITHIN GROUP(ORDER BY LEVEL DESC) bin FROM dual CONNECT BY POWER(2,LEVEL-1)<=&&N

Result (for N=12):

BIN ---------- 1100

Note that we sorted all the rows in descending order of the LEVEL to obtain the correct order of bits.

**My Oracle Group on Facebook:**

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

### Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book **“Oracle SQL Tricks and Workarounds”** for instructions.