Convert binary to decimal in the given sequence

Question

I have a requirement to convert the binary number to decimal. The converted decimal range would be no greater than 256. (100 Million in binary) It involves an iOS app, where to read a problem investigation report, four binary integers are dynamically chosen and base on tht. (Some UI stuffs inside with 0s and 1s). Then, we would concatenate them with '.' as delimiter.(similar to a IP address) Based on the final Sequence formed, we fetch the report id after converting them into decimals. Say, `10.100.1.11`(Input) could become `2.4.1.3`(Output) Have been going through numerous reports and Objective-C. Literally lost my mind. Being in iOS dev, I couldnt get a PL/SQL compiled. So, I have to accomplish with a SQL. EDIT: Luckily, I was able to write something like this. (but just one number at a time :( ) ``` SQL> var NUM number; SQL> exec :NUM := 100000000; PL/SQL procedure successfully completed. SQL> SELECT SUM(value) FROM (SELECT POWER(2,LENGTH(TO_CHAR(:NUM))-level)*to_number(SUBSTR(TO_C…

Accepted Answer

There are different ways you can approach this. So, I'm choosing what might seem like a rather arcane way. The motivation for this is that `to_number()` accepts hexadecimal formats but not binary formats. What would be so hard about supporting binary and octal as well as hex? Well, that's not a question for me to ask. Oracle doesn't.

But, we can sort of readily convert from binary to hex. You are only dealing with 8 binary digits, so that is only two hex digits. Here is the code:

```
with bin2hex as (
      select '0000' as bin, '0' as hex from dual union all
      select '0001' as bin, '1' as hex from dual union all
      select '0010' as bin, '2' as hex from dual union all
      select '0011' as bin, '3' as hex from dual union all
      select '0100' as bin, '4' as hex from dual union all
      select '0101' as bin, '5' as hex from dual union all
      select '0110' as bin, '6' as hex from dual union all
      select '0111' as bin, '7' as hex from dual union all
      select '1000' as bin, '8' as hex from dual union all
      select '1001' as bin, '9' as hex from dual union all
      select '1010' as bin, 'A' as hex from dual union all
      select '1011' as bin, 'B' as hex from dual union all
      select '1100' as bin, 'C' as hex from dual union all
      select '1101' as bin, 'D' as hex from dual union all
      select '1110' as bin, 'E' as hex from dual union all
      select '1111' as bin, 'F' as hex from dual
     )
select t.*, c1.bin as bin1, c2.bin as bin2, c1.hex as hex1, c2.hex as hex2,
       to_number(c2.hex||c1.hex, 'xx')
from (select '10010010' as num from dual union all
      select '10010' from dual
     ) t left outer join
     bin2hex c1
     on substr('00000000'||t.num, -4) = c1.bin left outer join
     bin2hex c2
     on substr('00000000'||t.num, -8, 4) = c2.bin;
```

Convert binary to decimal in the given sequence

Solution

Problem