Integer to Binary Conversion in Oracle SQL

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.