BASE36 encoder/decoder for Oracle Database

base36.pks

create or replace package base36 is

  -- Author  : RUSQ
  -- Created : 07.09.2009 19:07:38
  -- Purpose : Encodes/Decodes the base36 encoded number
  -- Licence : MIT

  -- Public function and procedure declarations
  function decode(row_id varchar2) return number;
  function encode(num number) return varchar2;

end base36;
/
create or replace package body base36 is

  function decode(row_id varchar2) return number is
    i           pls_integer;
    l_result    number(22) := 0;
    l_digit     char(1);
    l_digit_dec pls_integer;
  begin
    for i in 1 .. length(row_id) loop
      l_digit := substr(row_id, i, 1);
      if l_digit in
         ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z') then
        l_digit_dec := ASCII(l_digit) - ASCII('A') + 10;
      else
        l_digit_dec := to_number(l_digit);
      end if;
      l_result := l_result * 36 + l_digit_dec;
    end loop;
    return l_result;
  end;

  function encode(num number) return varchar2 is
    S varchar2(15) := '';
    n number(22) := num;
  begin
    loop
      select case
               when mod(n, 36) > 9 then
                chr(ascii('A') - 10 + mod(n, 36)) || S
               else
                to_char(mod(n, 36)) || S
             end
        into S
        from dual;
      n := trunc(n / 36);
      exit when n = 0;
    end loop;
    return S;
  end encode;
end base36;
/