Converting Bytes to KB, MB or GB

I was looking for some algorithm on how to convert Bytes to KB or MB or GB depending on the value. Most of the algorithm that I saw are using case statement to implement the breakdown as brackets.

Here's my own version. This will generate some random number that will be converted to KB, MB or GB. But this should help me in the future.

;with Sorted
as
(
   select 1 as base
   union all
   select base = base + 1
   from Sorted
   where base < 20
), Randomized
as
(
   select top 5 base
   from Sorted
   order by newid()
), SizeInStorage
as
(
select
   ByteSize = cast(abs(checksum(newid())) as bigint) 
from randomized
)
select
   ByteSize,
   Formatted = replace(convert(varchar(20), cast(ByteSize as money),1),'.00',''),
   Converted = coalesce(nullif(ByteSize/power(cast(1024 as bigint),4),0),
                        nullif(ByteSize/power(cast(1024 as bigint),3),0),
                        nullif(ByteSize/power(cast(1024 as bigint),2),0),
                        nullif(ByteSize/power(cast(1024 as bigint),1),0), 
                        ByteSize
                        ),
   Unit = coalesce('TB' + space(nullif(ByteSize/power(cast(1024 as bigint),4),0)), 
                   'GB' + space(nullif(ByteSize/power(cast(1024 as bigint),3),0)), 
                   'MB' + space(nullif(ByteSize/power(cast(1024 as bigint),2),0)), 
                   'KB' + space(nullif(ByteSize/power(cast(1024 as bigint),1),0)), 
                   'Bytes'
                  )
from SizeInStorage

Here's a sample result set:

ByteSize             Formatted       Converted   Unit
-------------------- --------------- ----------- -----
926528091            926,528,091     883         MB 
105563351            105,563,351     100         MB 
1603808599           1,603,808,599   1           GB 
866882480            866,882,480     826         MB 
426406913            426,406,913     406         MB 

Update...I want to convert bytes with two decimal places.

;with Sorted
as
(
   select 1 as base
   union all
   select base = base + 1
   from Sorted
   where base < 20
), Randomized
as
(
   select top 5 base
   from Sorted
   order by newid()
), SizeInStorage
as
(
select
   ByteSize = cast(abs(checksum(newid())) as bigint)
from randomized
union all
select top 1
   ByteSize = substring(cast(abs(checksum(newid())) as varchar(10)),1,5)
from randomized
union all
select top 1
   ByteSize = substring(cast(abs(checksum(newid())) as varchar(10)),1,3)
from randomized
)
select
   ByteSize,
   Formatted = replace(convert(varchar(20), cast(ByteSize as money),1),'.00',''),
   Converted = convert(varchar(30), isnull(cast(ByteSize as money)/ cast(coalesce((nullif((ByteSize/power(cast(1024 as bigint),4)),0)/nullif((ByteSize/power(cast(1024 as bigint),4)),0)) * power(cast(1024 as bigint),4),
                        (nullif((ByteSize/power(cast(1024 as bigint),3)),0)/nullif((ByteSize/power(cast(1024 as bigint),3)),0)) * power(cast(1024 as bigint),3),
                        (nullif((ByteSize/power(cast(1024 as bigint),2)),0)/nullif((ByteSize/power(cast(1024 as bigint),2)),0)) * power(cast(1024 as bigint),2),
                        (nullif((ByteSize/power(cast(1024 as bigint),1)),0)/nullif((ByteSize/power(cast(1024 as bigint),1)),0)) * power(cast(1024 as bigint),1)) as money)                                               
                        ,
                        ByteSize * 1.00
                           ), 1),
   Unit = coalesce('TB' + space(nullif(ByteSize/power(cast(1024 as bigint),4),0)),
                   'GB' + space(nullif(ByteSize/power(cast(1024 as bigint),3),0)),
                   'MB' + space(nullif(ByteSize/power(cast(1024 as bigint),2),0)),
                   'KB' + space(nullif(ByteSize/power(cast(1024 as bigint),1),0)),
                   'Bytes'
                  )
from SizeInStorage
 

Here's the resultset:

ByteSize      Formatted         Converted   Unit
------------  ----------------- ----------- ----
1582690891    1,582,690,891     1.47         GB
786337767     786,337,767       749.91       MB
431093153     431,093,153       411.12       MB
1067677070    1,067,677,070     1,018.22     MB
626880896     626,880,896       597.84       MB
28760         28,760            28.09        KB
308           308               308.00       Bytes

Comment?


~~ CK