Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
Returns the number of characters of the specified string expression, excluding trailing spaces.
To return the number of bytes used to represent an expression, use the DATALENGTH function.
LEN ( string_expression )
To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.
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 spaces. 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 a number that may not be equal to 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];
Use the LEN to return the number of characters encoded into a given string expression, and DATALENGTH to return the size in bytes for a given string expression. These outputs may differ depending on the data type and type of encoding used in the column. For more information on storage differences between different encoding types, see Collation and Unicode Support.
The following example selects the number of characters and the data in
FirstName for people located in
Australia. This example uses the AdventureWorks database.
SELECT LEN(FirstName) AS Length, FirstName, LastName FROM Sales.vIndividualCustomer WHERE CountryRegionName = 'Australia'; GO
Examples: Azure Synapse Analytics and Analytics Platform System (PDW)
The following example returns the number of characters in the column
FirstName and the first and last names of employees located in
USE AdventureWorks2016 GO 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';
Here is the result set.
FNameLength FirstName LastName ----------- --------- --------------- 4 Lynn Tsoflias