len-transact-sql.md

title	LEN (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	09/03/2015
ms.prod	sql-non-specified
ms.reviewer
ms.suite
ms.technology	database-engine
ms.tgt_pltfrm
ms.topic	language-reference
f1_keywords	LEN LEN_TSQL
dev_langs	TSQL
helpviewer_keywords	LEN function characters [SQL Server], number of number of characters
ms.assetid	fa20fee4-884d-4301-891a-c03e901345ae
caps.latest.revision	47
author	BYHAM
ms.author	rickbyh
manager	jhubbard

LEN (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-all_md]

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

Note

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

Transact-SQL Syntax Conventions

Syntax

-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse  
  
LEN ( string_expression )  

Arguments

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

Return Types

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.

Remarks

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);  
SELECT   
@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];  
  

Examples

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

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

Examples: [!INCLUDEssSDWfull] and [!INCLUDEssPDW]

The following example returns the number of characters in the column FirstName and the first and last names of employees located in Australia.

-- Uses AdventureWorks  
  
SELECT DISTINCT LEN(FirstName) AS FNameLength, FirstName, LastName   
FROM dbo.DimEmployee AS e  
INNER JOIN dbo.DimGeography AS g   
    ON e.SalesTerritoryKey = g.SalesTerritoryKey   
WHERE EnglishCountryRegionName = 'Australia';  

[!INCLUDEssResult]

FNameLength FirstName LastName

----------- --------- ---------------

4 Lynn Tsoflias

See Also

Data Types (Transact-SQL)
String Functions (Transact-SQL)
DATALENGTH (Transact-SQL)
LEFT (Transact-SQL)
RIGHT (Transact-SQL)