Base 36 to Base 10 conversion using SQL only
select sum(position_value) from
(
select power(36,position-1) * case when digit between '0' and '9'
then to_number(digit)
else 10 + ascii(digit) - ascii('A')
end
as position_value
from (
select substr(input_string,length(input_string)+1-level,1) digit,
level position
from (select '01Z' input_string from dual)
connect by level <= length(input_string)
)
)
For T-SQL the following logic will perform the task that the Oracle code above does. This is generic general solution and will support Base-X to Base-10:
select
sum(power(base,pos-1) *
case when substring(cnv,pos,1) between '0' and '9' then
cast(substring(cnv,pos,1) as int)
else 10 + ascii(upper(substring(cnv,pos,1))) - ascii('A') end)
from (values(reverse('01Z'), 36)) as t(cnv,base)
left join (values(1),(2),(3),(4),(5),(6)) as x(pos)
on pos <= len(cnv)
To use with other bases just use:
from (select cnv = reverse('FF'), base=16) as t
or
from (select cnv = reverse('101'), base=2) as t
Note that to support strings longer than 6 you would need to add more values to the position vector.