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

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