SQL Server Function to format bytes into larger units like MB, GB, TB, etc..
-- 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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment