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

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

Then set the Number to convert Like Below

1	`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

#SQL #CTE #common table expression #Number #Convert #Base #Base35 #Base2 #Base62 #SQLServer #Recursive #Math #Link #Shortener

4/6/2015 7:46:24 AM Tweet

Write Comment