Any suggestions? Export (0) Print
Expand All

LEN (Transact-SQL)


Updated: September 3, 2015

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the number of characters of the specified string expression, excluding trailing blanks.


To return the number of bytes used to represent an expression, use the DATALENGTH function.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

LEN ( string_expression )


Is the string expression to be evaluated. string_expression can be a constant, variable, or column of either character or binary data.

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

If you are using SC collations, the returned integer value counts UTF-16 surrogate pairs as a single character. For more information, see Collation and Unicode Support.

LEN excludes trailing blanks. If that is a problem, consider using the DATALENGTH (Transact-SQL) function which does not trim the string. If processing a unicode string, DATALENGTH will return twice the number of characters. The following example demonstrates LEN and DATALENGTH with a trailing space.

DECLARE @v1 varchar(40),
    @v2 nvarchar(40);
@v1 = 'Test of 22 characters ', 
@v2 = 'Test of 22 characters ';
SELECT LEN(@v1) AS [varchar LEN] , DATALENGTH(@v1) AS [varchar DATALENGTH];
SELECT LEN(@v2) AS [nvarchar LEN], DATALENGTH(@v2) AS [nvarchar DATALENGTH];

The following example selects the number of characters and the data in FirstName for people located in Australia. This example uses the AdventureWorks2012 database.

SELECT LEN(FirstName) AS Length, FirstName, LastName 
FROM Sales.vIndividualCustomer
WHERE CountryRegionName = 'Australia';

Community Additions

© 2015 Microsoft