[Solved] Conversion function in SQL Server

Question

Seems like you want to turn a base36 number into a base10 number.

Then you could create a function for base conversion.

The function

create function dbo.fnBase36ToBase10(@input varchar(8))
returns int
as
begin
  declare @base36string varchar(8) = upper(@input);
  declare @result int = 0;
  declare @basechars varchar(36) = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
  declare @N int = 36;
  declare @digit char(1);
  declare @pow int = 0;

  while @pow < len(@base36string)
  begin
    set @Digit = substring(reverse(@base36string),@Pow+1,1);
    set @result = @result + (charindex(@Digit, @basechars)-1) * power(@N, @pow);
    set @pow = @pow + 1;
  end
  return @result;
end;
GO

Example using the function

select code, (dbo.fnBase36ToBase10(code) - 234) as num
from (values
 ('99'),('9A'),('9B'),('9C')
,('9Z'),('A0')
,('A9'),('AA'),('AB')
) AS codes(code)
order by num;

Returns:

code    num
99      99
9A      100
9B      101
9C      102
9Z      125
A0      126
A9      135
AA      136
AB      137

And if creating a function isn’t an option.
Then you could join to a Tally Table that contains the conversion between the
Base36 and Base10 numbers.

Example:

IF OBJECT_ID('tempdb..#Base36ToBase10') IS NOT NULL DROP TABLE #Base36ToBase10;
CREATE TABLE #Base36ToBase10 (base36 varchar(2) COLLATE Latin1_General_CS_AS primary key, base10 int);

-- Fill Tally Table
;WITH CHARS AS 
(
    select 0 as n, convert(char(1),0) as c 
    union all 
    select n+1, iif(n<9,convert(char(1),n+1),char(n+56)) 
    from CHARS where n < 35
)
insert into #Base36ToBase10 (base36, base10)
select 
 concat(c2.c,c1.c) as b36, 
 (c2.n*36+c1.n) as b10
from CHARS c1
cross join CHARS c2
order by c2.c, c1.c;

declare @codes table (code varchar(2));
insert into @codes (code) values
 ('99'),('9A'),('9B'),('9C')
,('9Z'),('A0')
,('A9'),('AA'),('AB')
;

select code, (b36to10.base10 - 234) as num
from @codes AS codes
join #Base36ToBase10 as b36to10 
  on codes.code collate Latin1_General_CS_AS = b36to10.base36
order by num;

Accepted Answer