Home Library Learn Downloads Support Community
Sign in | United States - English | |
2 out of 2 rated this helpful Rate this topic

DATALENGTH (Transact-SQL)

SQL Server 2008 R2

Returns the number of bytes used to represent any expression.

Topic link icon Transact-SQL Syntax Conventions

Copy 

DATALENGTH ( expression )
expression

Is an expression of any data type.

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

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 Note

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

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

Copy 
USE AdventureWorks2008R2;
GO
SELECT length = DATALENGTH(Name), Name
FROM Production.Product
ORDER BY Name;
GO
Did you find this helpful?
(2000 characters remaining)
Community Content Add
Annotations FAQ
LEN vs DATALENGTH
Use DATALENGTH when the actual number of bytes is required, including trailing spaces.  So, in a double-byte encoding, you'll get actual bytes, not characters.  Use LEN if trailing spaces should not be counted and character count is required, independent of the byte encoding used.
History