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.

Get Sequence of Numbers Using CTE (Common Table Expression)

Declare @From bigint ,@to bigint
Select @From =10 ,@To =100 ;
With CTE(Number)as 
	(Select @From 
	union all 
	select Number+1 
		from CTE
		where Number<@to 
	)
select Number From CTE 
option (maxrecursion 0)

Result:

Number
10
11
12
13
14
15
.
.
.
.
.
.
92
93
94
95
96
97
98
99
100

Calculate First 182 Fibonaccis  (Fibonacci numbers) Using SQL Server CTE

with Fibonacci(ID,Prev,Number)as(
	select 1,cast(1 as Numeric(38)),cast(1 as Numeric(38))
	union all select ID+1, cast(Number as Numeric(38)) ,cast(Prev+Number as Numeric(38)) from Fibonacci where ID<182
	) 
select 'F('+cast(ID as nvarchar(50))+') = '+ cast(Number as nvarchar(50)) from Fibonacci
OPTION (MAXRECURSION 183);

Result:

F(1) = 1
F(2) = 2
F(3) = 3
F(4) = 5
F(5) = 8
F(6) = 13
F(7) = 21
F(8) = 34
F(9) = 55
F(10) = 89
F(11) = 144
F(12) = 233
F(13) = 377
F(14) = 610
F(15) = 987
F(16) = 1597
F(17) = 2584
F(18) = 4181
F(19) = 6765
F(20) = 10946
F(21) = 17711
F(22) = 28657
F(23) = 46368
F(24) = 75025
F(25) = 121393
F(26) = 196418
F(27) = 317811
F(28) = 514229
F(29) = 832040
F(30) = 1346269
F(31) = 2178309
F(32) = 3524578
F(33) = 5702887
F(34) = 9227465
F(35) = 14930352
F(36) = 24157817
F(37) = 39088169
F(38) = 63245986
F(39) = 102334155
F(40) = 165580141
F(41) = 267914296
F(42) = 433494437
F(43) = 701408733
F(44) = 1134903170
F(45) = 1836311903
F(46) = 2971215073
F(47) = 4807526976
F(48) = 7778742049
F(49) = 12586269025
F(50) = 20365011074
F(51) = 32951280099
F(52) = 53316291173
F(53) = 86267571272
F(54) = 139583862445
F(55) = 225851433717
F(56) = 365435296162
F(57) = 591286729879
F(58) = 956722026041
F(59) = 1548008755920
F(60) = 2504730781961
F(61) = 4052739537881
F(62) = 6557470319842
F(63) = 10610209857723
F(64) = 17167680177565
F(65) = 27777890035288
F(66) = 44945570212853
F(67) = 72723460248141
F(68) = 117669030460994
F(69) = 190392490709135
F(70) = 308061521170129
F(71) = 498454011879264
F(72) = 806515533049393
F(73) = 1304969544928657
F(74) = 2111485077978050
F(75) = 3416454622906707
F(76) = 5527939700884757
F(77) = 8944394323791464
F(78) = 14472334024676221
F(79) = 23416728348467685
F(80) = 37889062373143906
F(81) = 61305790721611591
F(82) = 99194853094755497
F(83) = 160500643816367088
F(84) = 259695496911122585
F(85) = 420196140727489673
F(86) = 679891637638612258
F(87) = 1100087778366101931
F(88) = 1779979416004714189
F(89) = 2880067194370816120
F(90) = 4660046610375530309
F(91) = 7540113804746346429
F(92) = 12200160415121876738
F(93) = 19740274219868223167
F(94) = 31940434634990099905
F(95) = 51680708854858323072
F(96) = 83621143489848422977
F(97) = 135301852344706746049
F(98) = 218922995834555169026
F(99) = 354224848179261915075
F(100) = 573147844013817084101
F(101) = 927372692193078999176
F(102) = 1500520536206896083277
F(103) = 2427893228399975082453
F(104) = 3928413764606871165730
F(105) = 6356306993006846248183
F(106) = 10284720757613717413913
F(107) = 16641027750620563662096
F(108) = 26925748508234281076009
F(109) = 43566776258854844738105
F(110) = 70492524767089125814114
F(111) = 114059301025943970552219
F(112) = 184551825793033096366333
F(113) = 298611126818977066918552
F(114) = 483162952612010163284885
F(115) = 781774079430987230203437
F(116) = 1264937032042997393488322
F(117) = 2046711111473984623691759
F(118) = 3311648143516982017180081
F(119) = 5358359254990966640871840
F(120) = 8670007398507948658051921
F(121) = 14028366653498915298923761
F(122) = 22698374052006863956975682
F(123) = 36726740705505779255899443
F(124) = 59425114757512643212875125
F(125) = 96151855463018422468774568
F(126) = 155576970220531065681649693
F(127) = 251728825683549488150424261
F(128) = 407305795904080553832073954
F(129) = 659034621587630041982498215
F(130) = 1066340417491710595814572169
F(131) = 1725375039079340637797070384
F(132) = 2791715456571051233611642553
F(133) = 4517090495650391871408712937
F(134) = 7308805952221443105020355490
F(135) = 11825896447871834976429068427
F(136) = 19134702400093278081449423917
F(137) = 30960598847965113057878492344
F(138) = 50095301248058391139327916261
F(139) = 81055900096023504197206408605
F(140) = 131151201344081895336534324866
F(141) = 212207101440105399533740733471
F(142) = 343358302784187294870275058337
F(143) = 555565404224292694404015791808
F(144) = 898923707008479989274290850145
F(145) = 1454489111232772683678306641953
F(146) = 2353412818241252672952597492098
F(147) = 3807901929474025356630904134051
F(148) = 6161314747715278029583501626149
F(149) = 9969216677189303386214405760200
F(150) = 16130531424904581415797907386349
F(151) = 26099748102093884802012313146549
F(152) = 42230279526998466217810220532898
F(153) = 68330027629092351019822533679447
F(154) = 110560307156090817237632754212345
F(155) = 178890334785183168257455287891792
F(156) = 289450641941273985495088042104137
F(157) = 468340976726457153752543329995929
F(158) = 757791618667731139247631372100066
F(159) = 1226132595394188293000174702095995
F(160) = 1983924214061919432247806074196061
F(161) = 3210056809456107725247980776292056
F(162) = 5193981023518027157495786850488117
F(163) = 8404037832974134882743767626780173
F(164) = 13598018856492162040239554477268290
F(165) = 22002056689466296922983322104048463
F(166) = 35600075545958458963222876581316753
F(167) = 57602132235424755886206198685365216
F(168) = 93202207781383214849429075266681969
F(169) = 150804340016807970735635273952047185
F(170) = 244006547798191185585064349218729154
F(171) = 394810887814999156320699623170776339
F(172) = 638817435613190341905763972389505493
F(173) = 1033628323428189498226463595560281832
F(174) = 1672445759041379840132227567949787325
F(175) = 2706074082469569338358691163510069157
F(176) = 4378519841510949178490918731459856482
F(177) = 7084593923980518516849609894969925639
F(178) = 11463113765491467695340528626429782121
F(179) = 18547707689471986212190138521399707760
F(180) = 30010821454963453907530667147829489881
F(181) = 48558529144435440119720805669229197641
F(182) = 78569350599398894027251472817058687522

Fast Way To Convert A Column to CSV.

Select replace	(
					(
					select name+','  from sys.databases  
						for xml path ('')
					)+',',',,',''
				) 

Result:

master,tempdb,model,msdb,Customers,ExternalDB