SQL Server Function to format bytes into larger units like MB, GB, TB, etc..

FormatBytes.sql

-- 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;