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.