Binary to Decimal

A while ago I wrote an article called A Faster Decimal to Binary Function, which explored creating a dec2bin function and the performance of several approaches. Well I was recently asked how I’d do the opposite, convert a binary input into decimal. It’s actually quite a trivial task, but anyway here’s my attempt at such a function.

create or replace function bin2dec(pBinval in varchar2) return pls_integer is
  vResult pls_integer;
begin
  if pBinval is not null then
    if translate(pBinval, 'a01', 'a') is not null then -- Check input is binary
      raise VALUE_ERROR;
    end if;
    vResult := 0;
    for i in 1..length(pBinval)
    loop
      vResult := (vResult * 2) + to_number(substr(pBinval, i, 1));
    end loop;
  end if;
  return vResult;
end;
/
 

Notes :
1. The translate(pBinval, ‘a01’, ‘a’) part validates that the input is a binary string. It’s a way of removing all of the 0s and 1s, leaving everything else in the string, which should be null for binary. The “a” is just a place holder to ensure anything else isn’t removed. “a” translates to “a”, “0” and “1” have no translation, so they are removed, and everything else not included remains as is.

2. The main part of the conversion code loops through all of the bits in reverse order, from most significant down to least significant bit. Multiplying the running result by 2 each loop, corrects for the reverse significant bit order, kind of like a shift left each time.

Now let’s run a few tests

-- Should give valid result of 4668
SQL> select bin2dec('1001000111100') dec from dual 
2 /

DEC
----------
4668

-- Should give value error
SQL> select bin2dec('10010002a111100') dec from dual 
2 /
select bin2dec('10010002a111100') dec from dual
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "PAULZIP.BIN2DEC", line 6

-- Should give null result
SQL> select bin2dec(null) dec from dual 
2 /

DEC
----------