Size of VARBINARY field in SQL Server 2005

I am trying to determine the size in bytes of the contents in a VARBINARY(MAX) field in SQL Server 2005, using SQL. As I doubt there is native support for this, could it be done using CLR integration? Any ideas would be greatly appreciated.

Actually, you can do this in T-SQL!

DATALENGTH(<fieldname>) will work on varbinary(max) fields.

The VARBINARY(MAX) field allocates variable length data up to just under 2GB in size.

You can use DATALENGTH() function to determine the length of the column content.

For example:

SELECT DATALENGTH(CompanyName), CompanyName
FROM Customers

CREATE FUNCTION [dbo].[FileDataSizeUnit_FN] (@FileData VARBINARY(MAX))
RETURNS VARCHAR(3)
AS
    BEGIN 
		DECLARE @Unit VARCHAR(3),
			@ByteLen AS NUMERIC(16,2)

		SET @ByteLen = ISNULL(DATALENGTH(@FileData),0)

        SET @Unit = CASE WHEN @ByteLen < 1000 THEN 'B'
		WHEN @ByteLen < 100000 THEN 'KB'
		WHEN @ByteLen < 1000000000 THEN 'MB'
		ELSE 'GB' END
	
        RETURN  @Unit
    END
GO

CREATE FUNCTION [dbo].[FileDataSize_FN] (@FileData VARBINARY(MAX))
RETURNS NUMERIC(16,2)
AS
    BEGIN 
		DECLARE @Size AS NUMERIC(16,2),
			@ByteLen AS NUMERIC(16,2)

		SET @ByteLen = ISNULL(DATALENGTH(@FileData),0)

        SET @Size = CASE WHEN @ByteLen <1000 THEN @ByteLen
		WHEN @ByteLen < 100000 THEN @ByteLen/1024.0 
		WHEN @ByteLen < 1000000000 THEN @ByteLen/1024.0/1024 
		ELSE CAST(@ByteLen/1024.0/1024/1024 AS NUMERIC(16,2)) END
	
        RETURN  @Size
    END
GO

SELECT dbo.FileDataSize_FN(F.FileData) AS Size,
		dbo.FileDataSizeUnit_FN(F.FileData) AS SizeUnit
	FROM dbo.Files_Tbl F