Convert array of 0 and 1 to base36 string

Question

Hello, everybody. Here is the thing. I have an array of 0 and 1. It is supposed to be a binary string. What i need is to format it to string that contains base36 conversion from that binary. In other words, i need to do this: array of 1 and 0 -> some kind of binary number -> convert to base36 number -> put it into string. How to do that?

Thousands of oil, 300 steel and +1 luck to answerers.

Answer 1

There is no Oracle built-in to do this sort of conversion. In the following example I use two functions witten by the inestimable Mr Kyte. to_dec() turns other bases to decimals, and to_base() turns decimals into other bases.

The procedure takes an array of ones and zeroes and returns a string of base 36.

create or replace type binary_nt as table of number(1,0);
/

create or replace function base2_to_base36 
    (p_onesnzeroes in binary_nt)
    return varchar2
is
    s_b2 varchar2(38);
    n_b10 pls_integer;
    s_b36 varchar2(38);
begin
    for i in 1..p_onesnzeroes.count()
    loop
        s_b2 := s_b2||trim(to_char(p_onesnzeroes(i)));
    end loop;

    n_b10 := to_dec(s_b2, 2);
    s_b36 := to_base(n_b10, 36);

    return s_b36;
end;
/

The proof of the pudding and all that ...

SQL> set serveroutput on size unlimited
SQL>     declare
  2          bins binary_nt := binary_nt(1,0,0,0,1,1,1,0);
  3          s varchar2(128);
  4      begin
  5          --  10001110 => 142  => 3Y
  6          s :=  base2_to_base36(bins);
  7          dbms_output.put_line(s);
  8      end;
  9      /
3Y

PL/SQL procedure successfully completed.

SQL>

edit

While I was assembling this sample you posted that your array of zeroes was ~450 entries long. This routine won't handle anything like that. It will hurl ORA-01426: numeric overflow way before you hit a number of that size.

edit 2

If you are happy to gamble with a little imprecision, you could replace the NUMBER variables with BINARY_DOUBLE variables (in both my sample and Tom's functions). That datatype can handle much bigger numbers. I cranked it up to array_count=470, which might look like this in base 36 :

EKQA1EJ6QB4SC8WOOWKWGGOS4KWWWWCS4WCW4SCWCOSOOS888K4CSC8SWO8OCKC8SSCWCOGK844CKG00SOW8KGS0CC4