-- 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]( 132413, 'geopbyte' ); -- select [dbo].[FormatBytes]( 1324135, 'geopbyte' ); --too big! create or alter function [dbo].[FormatBytes] ( @bytes decimal(38, 2), @toUnit nvarchar(15) = N'bytes' ) returns sysname with schemabinding as begin 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; declare @prefix decimal(38, 0) = --adjust precision to your needs. Can scale higher bytes the lower precision is 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;
Categories