Top
JohnKlann / How to  / SQL Convert Size in Bytes to Human Readable Units
sqlCode

SQL Convert Size in Bytes to Human Readable Units

Came across a lot of databases that hold size of objects, files, whatever in bytes which is not the easiest to glance at and easily understand. So I wrote a basic SQL function to convert size in bytes to human readable format. The goal being that the output is greater than or equal to 1.0, that is automatically gets loops until it is less than 1.0 then stops at the previous division allowing for an easy to understand yet automatic format. I also have it round to 2 decimal places.

--set database to be used
--USE BEDB;
--checks if function already exists if it does it drops and re-adds it otherwise it adds it
IF OBJECT_ID (N'dbo.f_ConvertSize') IS NOT NULL
    BEGIN
        DROP FUNCTION Dbo.F_ConvertSize;
        PRINT 'Function f_ConvertSize Alread Exists, Deleting it and Adding it back now'
    END
ELSE
    BEGIN
        PRINT 'Function f_ConvertSize Does Not Exist, Adding it now.'
    END

GO
--creates function that
--parameter: size in bytes
--returns: single value nvarchar of converted size to readable format
CREATE FUNCTION Dbo.F_ConvertSize ( @rawSize FLOAT )
Returns NVARCHAR(12)
    BEGIN
        --creates processing variables
        --div = value to divide by
        --counter = number of times divided
        DECLARE @div INT,@counter INT
        --unit is used to capture the unit measure used in the output
        DECLARE @unit NVARCHAR(3)
        --the sizes table variable is used to store the different units
        DECLARE @sizes TABLE (Id INT,Unit NVARCHAR(3))
        INSERT @sizes(Id,Unit)
        VALUES(1,' B'),(2,' KB'),(3,' MB'),(4,' GB'),(5,' TB')
        --sets div and counter
        SET @div = 1024
        SET @counter = 1
        --while loop used to perform the conversion with condition clause that requires the size to be greater or equal to 1
        WHILE @rawSize >= 1 AND @counter <= 5
            BEGIN
                --breaks if the size is less than 1 after division
                --else it sets the rawsize to the rawsize divided by the div then it sets the counter to +=1
                IF @rawSize/@div < 1
                    BREAK
                ELSE
                    SET @rawSize = @rawSize/@div
                    SET @counter += 1
            END
        --gets units used from the units table variable
        SELECT @unit = Unit
        FROM @sizes
        WHERE Id = @counter
        --converts the new size to varchar and rounds it to 2 decimal places then adds the units and returns the output
        RETURN CAST(ROUND(@rawSize,2) AS NVARCHAR(50))+@unit
    END
Submit your review
1
2
3
4
5
Submit
     
Cancel

Create your own review

Average rating:  
 0 reviews