Categories
Numbers SQL SQL Script Strings

SQL Server Function: How to format bytes into greater units like MB, GB, TB, etc

Here’s a link to my gist for a SQL script to return a string from bytes formatted as a larger unit.

Examples

select [dbo].[FormatBytes]( 1324.13, 'byte' );
select [dbo].[FormatBytes]( 13241322.567567, 'bytes' );
select [dbo].[FormatBytes]( 1324132255.567567, 'tb' );

Link: https://gist.github.com/Protiguous/c40ef32ee4fa0f4e93c9413d6cc3d6bd

select [dbo].[FormatBytes]( 13241322.567567, 'bytes' );
select [dbo].[FormatBytes]( 132413225.567567, 'bytes' );
select [dbo].[FormatBytes]( 1324132255.567567, 'bytes' );
select [dbo].[FormatBytes]( 13241322551.567567, 'bytes' );
select [dbo].[FormatBytes]( 13241322.567567, 'mb' );
select [dbo].[FormatBytes]( 132413225.567567, 'gb' );
select [dbo].[FormatBytes]( 1324132255.567567, 'tb' );
select [dbo].[FormatBytes]( 13241322551.567567, 'zb' );
select [dbo].[FormatBytes]( 13241322551.567567, 'yb' );
select [dbo].[FormatBytes]( 132413225512.567567, 'yb' );
select [dbo].[FormatBytes]( 132413225, 'bb' );
select [dbo].[FormatBytes]( 1324132253, 'bb' ); –too big!
select [dbo].[FormatBytes]( 1324.13, 'byte' );
select [dbo].[FormatBytes]( 1324135, 'geopbyte' ); –too big!
create or alter function [dbo].[FormatBytes] ( @bytes decimal(38, 0), @toUnit nvarchar(15) = N'bytes' )
returns sysname
with schemabinding
as
begin
declare @prefix decimal(38, 0); adjust precision to your needs. Can scale higher bytes the lower precision is.
set @toUnit = trim(@toUnit);
if @toUnit is null return null;
set @bytes = @bytes *
case @toUnit collate SQL_Latin1_General_CP1_CI_AI
when N'b' then 1
when N'byte' then 1
when N'bytes' then 1
when N'kb' then 1024
when N'kilobyte' then 1024
when N'kilobytes' then 1024
when N'mb' then 1048576
when N'megabyte' then 1048576
when N'megabytes' then 1048576
when N'gb' then 1073741824
when N'gigabyte' then 1073741824
when N'gigabytes' then 1073741824
when N'tb' then 1099511627776
when N'terabyte' then 1099511627776
when N'terabytes' then 1099511627776
when N'pb' then 1125899906842624
when N'petabyte'then 1125899906842624
when N'petabytes' then 1125899906842624
when N'eb' then 1152921504606846976
when N'exabyte' then 1152921504606846976
when N'exabytes' then 1152921504606846976
when N'zb' then 1180591620717411303424
when N'zettabyte'then 1180591620717411303424
when N'zettabytes' then 1180591620717411303424
when N'yb' then 1208925819614629174706176
when N'yottabyte' then 1208925819614629174706176
when N'yottabytes' then 1208925819614629174706176
when N'bb' then 1237940039285380274899124224
when N'brontobyte' then 1237940039285380274899124224
when N'brontobytes' then 1237940039285380274899124224
when N'geopbyte' then 1267650600228229401496703205376
when N'geopbytes' then 1267650600228229401496703205376
end;
set @prefix =
case
when abs(@bytes) < 1024 then @bytes bytes
when abs(@bytes) < 1048576 then (@bytes / 1024) kb
when abs(@bytes) < 1073741824 then (@bytes / 1048576) mb
when abs(@bytes) < 1099511627776 then (@bytes / 1073741824) gb
when abs(@bytes) < 1125899906842624 then (@bytes / 1099511627776) tb
when abs(@bytes) < 1152921504606846976 then (@bytes / 1125899906842624) pb
when abs(@bytes) < 1180591620717411303424 then (@bytes / 1152921504606846976) eb
when abs(@bytes) < 1208925819614629174706176 then (@bytes / 1180591620717411303424) zb
when abs(@bytes) < 1237940039285380274899124224 then (@bytes / 1208925819614629174706176) yb
when abs(@bytes) < 1267650600228229401496703205376 then (@bytes / 1237940039285380274899124224) bb
else (@bytes / 1267650600228229401496703205376) geopbytes
end;
return convert(sysname, @prefix) +
case
when abs(@bytes) < 1024 then N' Bytes'
when abs(@bytes) < 1048576 then N' KB'
when abs(@bytes) < 1073741824 then N' MB'
when abs(@bytes) < 1099511627776 then N' GB'
when abs(@bytes) < 1125899906842624 then N' TB'
when abs(@bytes) < 1152921504606846976 then N' PB'
when abs(@bytes) < 1180591620717411303424 then N' EB'
when abs(@bytes) < 1208925819614629174706176 then N' ZB'
when abs(@bytes) < 1237940039285380274899124224 then N' YB'
when abs(@bytes) < 1267650600228229401496703205376 then N' BB'
else N' geopbytes'
end;
end;
view raw FormatBytes.sql hosted with ❤ by GitHub

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s