(UPDATE: This article has been updated HERE based on some performance testing I conducted)
By far, the most popular article I've ever written is THIS article, where I wrote a function to take a number and a byte-based unit of measure and then convert it to the largest whole unit of measure in a human readible format. However, I've been getting a lot of people reading that who were actually looking for a function to convert between any two byte-based units of measure like Gigabytes to Terabytes or Yottabytes to Bytes, etc. for those times when the output needed to be in a specific UOM.
Well, I've gotten enough requests for this that I decided to make my own function which will convert between any two byte-based units of measure for those people that must have the output in a certain UOM, or just want to know how many bytes in a Geopbyte.
OVERVIEW
Essentially, the process goes like this:
-
Using some sort of lookup table or giant CASE statement (eew), find out how many bytes are in the input UOM
(example: GB = 1073741824)
and use that number as a multiplier in the next step
-
Convert the input number to bytes using that multiplier.
-
Using the same sort of lookup as above, find out how many bytes are in the output UOM
(example MB = 1048576)
and use that number as a divisor in the next step
-
Convert the bytes from step 2 (which represents the input number in Bytes) to the output unit of measure using the multiplier from step 3
-
Add a suffix indicating what the UOM is (MB, GB, TB, etc.) and return that whole string
THE DETAILS (Finished script is HERE, but read this article to understand it first.)
So, the details of implementing this can be a little different depending on what you have access to do. For example, I think ideally we should create a lookup table (an actual, physical table) in the DB which will be used for steps 1, 3 above. But you may be one of those who have SQL managed by another group and they won't give you access to create this table and won't do it for you. So, you may need to use the tempDB or a temp table or a @table variable or [gasp] a big CASE statement to do the same thing. I'll show you the ideal way first, and if there's time, I'll show an alternative.
1. THE LOOKUP TABLE
First thing we need to do is create the lookup table which will hold all of the possible byte-based units of measure that we could possibly use.
USE [yourDB]
GO
-- CREATE A CONVERSION LOOKUP TABLE
CREATE TABLE dbo._SDE_ByteConversion (
UOM VARCHAR(16) PRIMARY KEY,
Value DECIMAL(38,0) NOT NULL,
Suffix VARCHAR(16) NOT NULL
);
GO
A few things to notice: We've got the UOM, we've got a Value (which will be the number of bytes in one of those units of measure) and we've got a suffix (which represents an abbreviation of the unit of measure...like the string 'KB' or 'MB', etc. Now, Geopbytes can't really be abbreviated, because GB was already taken, so I've just spelled it all out. Same with Bytes. I just say Bytes so there's no mistaking what the B means)
Now, let's populate the table with values (note, there's a lot of ways to do this, this way should be compatible with SQL 2000, 2005 & 2008 for sure. If I thought everyone would be at SQL 2008, I'd write it a little differently so it was all one statement, but I know there's a lot of cheap buggers out there :)
--POPULATE THE TABLE WITH UOM VALUES
INSERT INTO dbo._SDE_ByteConversion VALUES('Bytes',1,' Bytes');
INSERT INTO dbo._SDE_ByteConversion VALUES('Byte',1,' Bytes');
INSERT INTO dbo._SDE_ByteConversion VALUES('B',1,' Bytes');
INSERT INTO dbo._SDE_ByteConversion VALUES('Kilobytes',1024,' KB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Kilobyte',1024,' KB');
INSERT INTO dbo._SDE_ByteConversion VALUES('KB',1024,' KB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Megabytes',1048576,' MB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Megabyte',1048576,' MB');
INSERT INTO dbo._SDE_ByteConversion VALUES('MB',1048576,' MB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Gigabytes',1073741824,' GB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Gigabyte',1073741824,' GB');
INSERT INTO dbo._SDE_ByteConversion VALUES('GB',1073741824,' GB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Terabytes',1099511627776,' TB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Terabyte',1099511627776,' TB');
INSERT INTO dbo._SDE_ByteConversion VALUES('TB',1099511627776,' TB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Petabytes',1125899906842624,' PB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Petabyte',1125899906842624,' PB');
INSERT INTO dbo._SDE_ByteConversion VALUES('PB',1125899906842624,' PB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Exabytes',1152921504606846976,' EB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Exabyte',1152921504606846976,' EB');
INSERT INTO dbo._SDE_ByteConversion VALUES('EB',1152921504606846976,' EB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Zettabytes',1180591620717411303424,' ZB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Zettabyte',1180591620717411303424,' ZB');
INSERT INTO dbo._SDE_ByteConversion VALUES('ZB',1180591620717411303424,' ZB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Yottabytes',1208925819614629174706176,' YB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Yottabyte',1208925819614629174706176,' YB');
INSERT INTO dbo._SDE_ByteConversion VALUES('YB',1208925819614629174706176,' YB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Brontobytes',1237940039285380274899124224,' BB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Brontobyte',1237940039285380274899124224,' BB');
INSERT INTO dbo._SDE_ByteConversion VALUES('BB',1237940039285380274899124224,' BB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Geopbytes',1267650600228229401496703205376,' GeopBytes');
INSERT INTO dbo._SDE_ByteConversion VALUES('Geopbyte',1267650600228229401496703205376,' GeopBytes');
GO
2,3,4 & 5. THE CONVERSION
Now that our lookup table is created, we can use it in our function
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Number2 (John Nelson) - http://number2blog.com
-- Create date: 2009-07-02
-- Description: Convert bytes from one byte-based unit of measure
-- (Bytes, KB, MB, GB, TB, PB, EB, ZB, YB, BB, Geopbytes)
-- to any other byte-based unit of measure in human-
-- readable format...meaning the output is a number
-- followed by a UOM, not just a number.
-- example: 21.409023 GB
-- INPUT: @InputNumber - Decimal(38,7)
-- @InputUOM - VARCHAR(11)
-- @OutputUOM - VARCHAR(11)
--
-- OUTPUT: VARCHAR(64)
--
-- DEPENDENCIES: Requires a table called dbo._SDE_ByteConversion
-- which holds the UOM, the value and the human-readable UOM suffix
-- (MB, GB, TB, etc)
-- =============================================
CREATE FUNCTION [dbo].[udf_ConvertBytes]
(
@InputNumber DECIMAL(38,7),
@InputUOM VARCHAR(11) = 'Bytes',
@OutputUOM VARCHAR(11) = 'Gigabytes'
)
RETURNS VARCHAR(64)
WITH SCHEMABINDING
AS
BEGIN
--convert the inputnumber to bytes (the subselect) and then divide the
--bytes by the value of the OutputUOM and add the suffix to do the conversion
RETURN (
SELECT CAST((SELECT Value * @InputNumber FROM dbo._SDE_ByteConversion WHERE UOM = @InputUOM)/Value AS VARCHAR(49)) + Suffix
FROM dbo._SDE_ByteConversion
WHERE UOM = @OutputUOM
)
END;
OK, steps 2,3,4 & 5 are embedded in that one return statement for the function, so let's break it down
2 - Convert the input number to bytes
SELECT CAST((SELECT Value * @InputNumber FROM dbo._SDE_ByteConversion WHERE UOM = @InputUOM)/Value AS VARCHAR(49)) + Suffix
FROM dbo._SDE_ByteConversion
WHERE UOM = @OutputUOM
In this subquery (in blue), we're checking the lookup table for the VALUE (number of bytes) for the input UOM passed in and then multiplying that value by the input number (Value * @InputNumber) to get the total bytes represented by that input number.
3 - Lookup the bytes in the output UOM
SELECT CAST((StuffFromStep2)/Value AS VARCHAR(49)) + Suffix
FROM dbo._SDE_ByteConversion
WHERE UOM = @OutputUOM
One of the things the outer select statement gets is the VALUE for the output UOM from the lookup table.
4 - Convert the bytes from step 2 to the UOM of step 3
SELECT CAST((StuffFromStep2)/Value AS VARCHAR(49)) + Suffix
FROM dbo._SDE_ByteConversion
WHERE UOM = @OutputUOM
Simply dividing the Value we got from step 3 from the bytes we got on step 2 will give us the number of output units.
SELECT CAST((StuffFromStep2)/Value AS VARCHAR(49)) + Suffix
FROM dbo._SDE_ByteConversion
WHERE UOM = @OutputUOM
This CAST statement is just to convert the numeric data from step 4 into VARCHAR data so we can append the suffix to it in step 5. If you don't do this, it tries to do addition with the data from step 4 and the suffix from step 5 (which is VARCHAR) and it will give an error.
5 - Add a suffix to the value
SELECT CAST((StuffFromStep2)/Value AS VARCHAR(49)) + Suffix
FROM dbo._SDE_ByteConversion
WHERE UOM = @OutputUOM
Another thing the outer select statement gets is the SUFFIX value from the lookup table for the output UOM, which is an easy to understand abbreviation for the different units of measure. Instead of appending 'Gigabytes' to the end of the numeric value, we would use 'GB'. So, since the value from step 4 was converted to VARCHAR data, now when we add the suffix to the end we're doing string concatenation.
So that's it really. If we want to have this function and table accessible to SMS/ConfigManager reports we also need to grant SELECT/EXECUTE rights to the table and/or function.
SUMMARY AND NEXT STEPS
HERE is the finished SQL script beginning to end, including the proper rights granting.
So now we have a SQL function that uses a lookup table to know how to convert from one byte-based unit of measure to another byte-based unit of measure and output it nicely.
What this may still need is different output formats (right now the number portion of the output is DECIMAL(38,7) which has a lot of numbers after the decimal point. If you need that to look differently, then you need to do something about it. Also, if you don't want the VARCHAR() output complete with the unit of measure abbreviation, you can modify it to remove that suffix addition of step 5 and the CAST of step 4.
IF YOU CAN'T CREATE YOUR OWN LOOKUP TABLE
If you aren't allowed to create the dbo._SDE_ByteConversion table or don't want to, there are a few options. You can either create a @temp variable that will hold the values and simulate the permanent lookup table, or you could set up some variables in this function and use a bunch of case statements to do the UOM value lookup. Something like this:
DECLARE @InputMultiplier DECIMAL(38,0);
DECLARE @OutputDivisor DECIMAL(38,0);
DECLARE @OutputSuffix VARCHAR(11);
SELECT
@InputMultiplier =
CASE @InputUOM
WHEN 'Bytes' THEN 1
WHEN 'KB' THEN 1024
WHEN 'MB' THEN 1048576
...
END,
@OutputDivisor =
CASE @OutputUOM
WHEN 'Bytes' THEN 1
WHEN 'KB' THEN 1024
WHEN 'MB' THEN 1048576
...
END,
@OutputSuffix =
CASE @OutputUOM
WHEN 'Bytes' THEN 'Bytes'
WHEN 'KB' THEN 'KB'
WHEN 'MB' THEN 'MB'
...
END
SELECT CAST((@InputNumber * @InputMultiplier)/@OutputDivisor AS VARCHAR(49)) + @OutputSuffix
Well, that's all I have time for
Number2 (John Nelson)
MyITForum - Forum Posts
MyITForum - Blog
