BSW.Blog

Convert Bigint Numbers to any Base using TSQL and Common Table Expressions

Here Is Small Query to Convert Numbers to Desired Base:

All the things needed is:

Set the available digits in your desired base as below

Select @digits ='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',@base = LEN (@Digits ) ,  @res='';

Then set the Number to convert Like Below

Select @Number=500000000000;

Common table expression query is :

Declare @Base Int , @res Nvarchar(50) ,@Digits Nvarchar (100)
declare @Number bigint 
Select @digits ='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',@base = LEN (@Digits ) ,  @res='';
select @Base as Base 
Select @Number=500000000000;
with cte(ID, Number , Module) as 
	(Select 1 ,@Number , cast (0 as int)
		Union all 
		Select cte.ID +1 , (floor(Number/@base)  ) , cast(Number%@Base as int )  from CTE
		where number>0  
)
select @res=substring(@digits,Module +1  , 1)+@res   From Cte where ID>1  order By ID 
select @res  as Result 

result

Base
-----------
62

(1 row(s) affected)

Result
--------------------------------------------------
8nlt2n2

(1 row(s) affected)

if you change the digit to 0 and 1 you can have base 2 Conversion and also for each base you can change the digits to available digits in that base.