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.
Babak