SQL Function - Convert Bytes to KB, MB, GB, TB, PB or EB and format the output to human readable format

(UPDATE:  I've created a similar function that will convert any byte based unit of measure to any other byte based unit of measure HERE

When you query a bunch of SMS/SCCM data that has it's output in bytes or KB or MB, often times you want that expressed in a more human readable format. This function takes 2 parameters:  a number, and the unit of measurement that number is in.

Examples:
Number: 1024   
UOM: Bytes
Results:  1.00 KB

Number: 0.5    
UOM: GB
Results:   512.00 MB

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Number2 (John Nelson) - http://number2blog.com
-- Create date: 01-10-2006
-- Modified:    09-04-2008 - Added ZB, YB, BB, GeopBytes and allow larger input numbers
-- Description: Format bytes as largest unit (KB, MB, GB, TB, PB, EB, ZB, YB, BB, Geopbytes)
--              Will take any unit of measurement as input (bytes,
--              KB, MB, GB, TB, PB, EB, ZB, YB, BB, Geopbytes) and converts them first to
--              bytes, then formats that as the largest whole UOM in
--              a human readable format.  1024MB becomes 1.00GB, 0.5GB
--              becomes 512MB, etc.
-- INPUT: @InputNumber - Decimal(38,2)
-- =============================================
ALTER FUNCTION [dbo].[udf_FormatBytes]
(
   @InputNumber   DECIMAL(38,7),
   @InputUOM      VARCHAR(5) = 'Bytes'
)
RETURNS VARCHAR(20)
WITH SCHEMABINDING
AS
BEGIN
   -- Declare the return variable here
   DECLARE @Output VARCHAR(48)
   DECLARE @Prefix MONEY
   DECLARE @Suffix VARCHAR(6)
   DECLARE @Multiplier DECIMAL(38,2)
   DECLARE @Bytes  DECIMAL(38,2)

   SELECT @Multiplier =
      CASE @InputUOM
         WHEN 'Bytes'         THEN 1
         WHEN 'Byte'          THEN 1
         WHEN 'B'             THEN 1
         WHEN 'Kilobytes'     THEN 1024
         WHEN 'Kilobyte'      THEN 1024
         WHEN 'KB'            THEN 1024
         WHEN 'Megabytes'     THEN 1048576
         WHEN 'Megabyte'      THEN 1048576
         WHEN 'MB'            THEN 1048576
         WHEN 'Gigabytes'     THEN 1073741824
         WHEN 'Gigabyte'      THEN 1073741824
         WHEN 'GB'            THEN 1073741824
         WHEN 'Terabytes'     THEN 1099511627776
         WHEN 'Terabyte'      THEN 1099511627776
         WHEN 'TB'            THEN 1099511627776
         WHEN 'Petabytes'     THEN 1125899906842624
         WHEN 'Petabyte'      THEN 1125899906842624
         WHEN 'PB'            THEN 1125899906842624
         WHEN 'Exabytes'      THEN 1152921504606846976
         WHEN 'Exabyte'       THEN 1152921504606846976
         WHEN 'EB'            THEN 1152921504606846976
         WHEN 'Zettabytes'    THEN 1180591620717411303424
         WHEN 'Zettabyte'     THEN 1180591620717411303424
         WHEN 'ZB'            THEN 1180591620717411303424
         WHEN 'Yottabytes'    THEN 1208925819614629174706176
         WHEN 'Yottabyte'     THEN 1208925819614629174706176
         WHEN 'YB'            THEN 1208925819614629174706176
         WHEN 'Brontobytes'   THEN 1237940039285380274899124224
         WHEN 'Brontobyte'    THEN 1237940039285380274899124224
         WHEN 'BB'            THEN 1237940039285380274899124224
         WHEN 'Geopbytes'     THEN 1267650600228229401496703205376
         WHEN 'Geopbyte'      THEN 1267650600228229401496703205376
      END

   SELECT @Bytes = @InputNumber*@Multiplier

   SELECT @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,
          @Suffix =
     CASE
         WHEN ABS(@Bytes) < 1024 THEN ' Bytes'
         WHEN ABS(@Bytes) < 1048576 THEN ' KB'
         WHEN ABS(@Bytes) < 1073741824 THEN ' MB'
         WHEN ABS(@Bytes) < 1099511627776 THEN ' GB'
         WHEN ABS(@Bytes) < 1125899906842624 THEN ' TB'
         WHEN ABS(@Bytes) < 1152921504606846976 THEN ' PB'
         WHEN ABS(@Bytes) < 1180591620717411303424 THEN ' EB'        
         WHEN ABS(@Bytes) < 1208925819614629174706176 THEN ' ZB'        
         WHEN ABS(@Bytes) < 1237940039285380274899124224 THEN ' YB'        
         WHEN ABS(@Bytes) < 1267650600228229401496703205376 THEN ' BB'        
         ELSE ' Geopbytes'
      END

   -- Return the result of the function
   SELECT @Output = CAST(@Prefix AS VARCHAR(39)) + @Suffix
   RETURN @Output

END

GO

-- GIVE PERMISSIONS FOR WEB REPORTS
GRANT EXECUTE ON dbo.udf_FormatBytes TO webreport_approle, smsschm_users
GO

-- NOW TEST IT

SELECT dbo.udf_FormatBytes(22345,'Bytes')

SELECT dbo.udf_FormatBytes(8675309,'MB')

SELECT dbo.udf_FormatBytes(0.5,'GB')
 

 

Number2 (John Nelson)
MyITForum - Forum Posts
MyITForum - Blog
Add to Google

Published Friday, January 11, 2008 1:14 AM by jnelson

Comments

# re: SQL Function - Convert Bytes to KB, MB, GB, TB, PB or EB and format the output to human readable format

Sunday, May 03, 2009 4:42 PM by Lightshards

Nice...

 Thank you for this information.

is Yottabyte Pronounced eye-owe-ta-byte'?

# re: SQL Function - Convert Bytes to KB, MB, GB, TB, PB or EB and format the output to human readable format

Tuesday, July 07, 2009 9:49 AM by jnelson

Actually, I think it's ee-otta, as in "yadda, yadda, yadda"

# SQL Query: How to convert a byte value to any other byte-based value

Wednesday, July 08, 2009 4:22 AM by Robs Blog - MVP

John Nelson (MVP ConfigMgr) has turned out a really cool SQL query, which converts a byte value in to

# SQL Query: How to convert a byte value to any other byte-based value

Wednesday, July 08, 2009 4:23 AM by Rob Marshall @ MyItForum.com

John Nelson (MVP ConfigMgr) has turned out a really cool SQL query, which converts a byte value in to

# SQL Query: How to convert a byte value to any other byte-based value

Wednesday, July 08, 2009 4:23 AM by Robs Blog - MVP

John Nelson (MVP ConfigMgr) has turned out a really cool SQL query, which converts a byte value in to

Powered by Community Server (Commercial Edition), by Telligent Systems