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
LEN ( string_expression )
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);
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];
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
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';
Here is the result set.
FNameLength FirstName LastName
----------- --------- ---------------
4 Lynn Tsoflias
DATALENGTH (Transact-SQL)
CHARINDEX (Transact-SQL)
PATINDEX (Transact-SQL)
LEFT (Transact-SQL)
RIGHT (Transact-SQL)
Data Types (Transact-SQL)
String Functions (Transact-SQL)