Just for fun, I made a block of code that will convert decimal numbers to any other number system. Maybe I’ll go the other direction next. It’s basically a loop. It takes the remainder of the original number divided by the number system (MOD) and adds that to a string. The string is made by putting in the new number first, then the rest of the pre-existing number is concatenated on the end.

When I made the jump over decimal numbers to new pastures, I added the CASE statement. If the remainder is greater than 9 then we need to replace the number with a letter. 10 = A, 11 = B, 12 = C, and so on. I get the number by taking the remainder, substracting 9, then adding that to 64. That way, 10-9 = 1, then 64 + 1 = 65 and finally, CHR(65) = A. I’ve tested the function up to Base-32 and it all works fine!

declare
 val number;
 vConv varchar2(20);
 vSystem number;
begin
 val := 110; --Decimal to Convert
 vSystem := 32; --System to Use

 while val > vSystem loop
     case when to_char(mod(val,vSystem)) > 9 then
         vConv := chr(64+(to_char(mod(val,vSystem))-9)) || vConv;
     else 
         vConv := to_char(mod(val,vSystem)) || vConv;
     end case;
     val := floor(val/vSystem);
 end loop;
 vConv := to_char(val) || vConv;
 
 dbms_output.put_line('Converted: (' || vConv || ')'|| to_char(vSystem));
 
end;

MOD (dividend,divisor) – Returns the remainder of a division operation.

10 ÷ 3 = 3 with a remainder of 1. The remainder of 1 is what MOD returns.

REMAINDER (dividend,divisor) – is similar to MOD but with some important differences. See here.

select mod(10,3) from dual;

 MOD(8,3)
----------
 1
1 row selected.

select remainder(10,3) from dual;

REMAINDER(10,3)
---------------
 1
1 row selected.

FLOOR(number) – Returns the largest integer that is equal to or less than the number passed. An integer is a number that can be written without a fractional component. It works the same as

TRUNC(number,decimal-places) - This could be used instead of FLOOR if you truncate the number to 0 decimal places.

select floor(9.999) from dual;

FLOOR(9.999)
------------
 9
1 row selected.

select trunc(9.999,0) from dual;

TRUNC(9.999,0)
--------------
 9
1 row selected.

select trunc(9.999,1) from dual;

TRUNC(9.999,1)
--------------
 9.9
1 row selected.

CHR (number) – Returns the alpha character that corresponds to the number passed in the character set used by the database. The database is using the standard ASCII character set. You can change which character set is used as an optional parameter.

select chr(65) from dual;

CHR(65)
-------
A 
1 row selected.

ASCII(string) – This is the opposite of the CHR function. If I pass in a character, I can get the ASCII code number.

select ascii('A') from dual;

ASCII('A')
----------
 65
1 row selected.