Calculate size in megabytes from SQL varbinary field

Question

We have a db table that stores files as varbinary(MAX). When we run the following script:

SELECT SUM(LEN(Content)) FROM dbo.File 

The result is:

35398663

I want to convert this number into megabytes? Is this possible?

Answer 1

Use DATALENGTH to retrieve the number of bytes and then convert, like this:

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE supportContacts      (      id int identity primary key,       type varchar(20),       details varchar(30)     );  INSERT INTO supportContacts (type, details) VALUES ('Email', '[email protected]'), ('Twitter', '@sqlfiddle'); 

Query 1:

select *, gigabytes / 1024.0 as terabytes from (   select *, megabytes / 1024.0 as gigabytes   from (     select *, kilobytes / 1024.0 as megabytes     from (       select *, bytes / 1024.0 as kilobytes       from (         select sum(datalength(details)) as bytes         from supportContacts              ) a     ) b     ) c ) d 

Results:

| bytes | kilobytes |     megabytes |      gigabytes |         terabytes | |-------|-----------|---------------|----------------|-------------------| |    29 |   0.02832 | 0.00002765625 | 2.700805664e-8 | 2.63750553125e-11 |