Skip to content

len-transact-sql.md

Latest commit

 

History

History
118 lines (87 loc) · 4.4 KB

len-transact-sql.md

File metadata and controls

118 lines (87 loc) · 4.4 KB
title LEN (Transact-SQL)
description LEN returns the number of characters of the specified string expression, excluding trailing spaces.
author rwestMSFT
ms.author randolphwest
ms.date 10/20/2025
ms.service sql
ms.subservice t-sql
ms.topic reference
ms.custom
ignite-2025
f1_keywords
LEN
LEN_TSQL
helpviewer_keywords
LEN function
characters [SQL Server], number of
number of characters
dev_langs
TSQL
monikerRange >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric || =fabric-sqldb

LEN (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw-fabricsqldb]

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

Note

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

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

LEN ( string_expression )

Arguments

string_expression

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're 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 spaces. If that is a problem, consider using the DATALENGTH function, which doesn't trim the string. If processing a unicode string, DATALENGTH returns a number that might not be equal to the number of characters. The following example demonstrates LEN and DATALENGTH with a trailing space.

DECLARE @v1 AS VARCHAR (40), @v2 AS 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];

Note

Use 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 might 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.

Examples

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 name (FirstName) and family name (LastName) of employees located in Australia.

USE AdventureWorks2022;
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';

[!INCLUDE ssResult]

FNameLength  FirstName  LastName
-----------  ---------  ---------------
4            Lynn       Tsoflias

Related content