SQL Function to convert bytes to string

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

create or alter function [dbo].[FormatBytes] ( @Number decimal(38, 7), @InputUOM nvarchar(15) = N'bytes' )
returns nvarchar(20)
with SCHEMABINDING
as
begin
	declare @Multiplier decimal(38,2) =
		case @InputUOM 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 @Bytes decimal(38,2) = @Number * @Multiplier;

	declare @prefix money =
		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;

	declare @suffix nvarchar(15) =
		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;

	return CAST(@prefix as nvarchar(42)) + @suffix;
end;

Author: Protiguous

C# Software Developer, Father, and seeker of Truth. View all posts by Protiguous