DATALENGTH (Transact-SQL)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Returns the number of bytes used to represent any expression.

Topic link icon Transact-SQL Syntax Conventions

Syntax

-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse  

DATALENGTH ( expression )   

Arguments

expression
Is an expression of any data type.

Return Types

bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types; otherwise int.

Remarks

DATALENGTH is especially useful with varchar, varbinary, text, image, nvarchar, and ntext data types because these data types can store variable-length data.

The DATALENGTH of NULL is NULL.

Note

Compatibility levels can affect return values. For more information about compatibility levels, see ALTER DATABASE Compatibility Level (Transact-SQL).

Examples

The following example finds the length of the Name column in the Product table.

USE AdventureWorks2012;  
GO  
SELECT length = DATALENGTH(Name), Name  
FROM Production.Product  
ORDER BY Name;  
GO  

Examples: Azure SQL Data Warehouse and Parallel Data Warehouse

The following example finds the length of the Name column in the Product table.

-- Uses AdventureWorks  

SELECT length = DATALENGTH(EnglishProductName), EnglishProductName  
FROM dbo.DimProduct  
ORDER BY EnglishProductName;  
GO  

See Also

LEN (Transact-SQL)
CAST and CONVERT (Transact-SQL)
Data Types (Transact-SQL)
System Functions (Transact-SQL)