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