Allow me to present dec2base() and base2dec(), a pair of MySQL stored procedures for converting to/from denary and other number bases. The examples below are for Base 53. Just add or remove characters from t_digits to change.
If you are curious about my choice of Base 53, it is because I have tried to minimise the use of similar-looking characters, to reduce transcription errors.
This code is based on an example in PL/SQL that I found on the JavaConfessions blog.
The reason for using varbinary() instead of varchar() for t_digits is that base2dec() will produce incorrect results due to case-insensitive matches on varchar().
drop procedure if exists dec2base;
delimiter //
create procedure dec2base(n int unsigned)
begin
declare t_modulo int unsigned;
declare t_int int unsigned;
declare t_val varchar(256);
declare t_char char(1);
declare t_digits varbinary(256);
declare t_base int unsigned;
set t_digits='23456789abcdefghjkmnpqrstuvwxyABCDEFGHJKLMNPQRSTUVWXY';
set t_base=length(t_digits);
if (n=0) then
select 0;
else
set t_int=n;
set t_val='';
theLoop: loop
if (t_int=0) then
leave theLoop;
end if;
set t_modulo = t_int % t_base;
set t_char = substr(t_digits, t_modulo+1, 1);
set t_val = concat(t_char,t_val);
set t_int = floor(t_int/t_base);
end loop;
select t_val as base_value;
end if;
end;
//
delimiter ;
drop procedure if exists base2dec;
delimiter //
create procedure base2dec(c varbinary(256))
begin
declare t_iterator int unsigned;
declare t_length int unsigned;
declare t_char char(1);
declare t_int int unsigned;
declare t_retval int unsigned default 0;
declare t_mult int unsigned default 1;
declare t_convval varbinary(256);
declare t_digits varbinary(256);
declare t_base int unsigned;
set t_digits='23456789abcdefghjkmnpqrstuvwxyABCDEFGHJKLMNPQRSTUVWXY';
set t_base=length(t_digits);
set t_convval=c;
set t_length=length(t_convval);
set t_iterator=t_length;
theLoop: loop
if (t_iterator=0) then
leave theLoop;
end if;
set t_char = substr(t_convval,t_iterator,1);
set t_int = instr(t_digits,t_char)-1;
set t_retval = t_retval + (t_int * t_mult);
set t_mult = t_mult * t_base;
set t_iterator = t_iterator -1;
end loop;
select t_retval as decimal_value;
end;
//
delimiter ;
Published Friday, 18th September 2009.
from 