Skip to content

len-transact-sql.md

Latest commit

 

History

History
113 lines (90 loc) · 4.55 KB

len-transact-sql.md

File metadata and controls

113 lines (90 loc) · 4.55 KB

title: "LEN (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: "09/03/2015" ms.prod: "sql" ms.prod_service: "database-engine, sql-database, sql-data-warehouse, pdw" ms.service: "" ms.component: "t-sql|functions" ms.reviewer: "" ms.suite: "sql" 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: "edmacauley" ms.author: "edmaca" manager: "craigg" ms.workload: "Active" monikerRange: ">= aps-pdw-2016 || = azuresqldb-current || = azure-sqldw-latest || >= sql-server-2016 || = sqlallproducts-allversions"

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.

Topic link icon Transact-SQL Syntax Conventions

Syntax

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

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