Home Library Learn Downloads Support Community
Sign in | United States - English | |
2 out of 2 rated this helpful - Rate this topic

LEN (Transact-SQL)

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

Note Note

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

Topic link icon Transact-SQL Syntax Conventions

Copy 

          

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.

The following example selects the number of characters and the data in FirstName for people located in Australia.

Copy 
USE AdventureWorks2012;
GO
SELECT LEN(FirstName) AS Length, FirstName, LastName 
FROM Sales.vIndividualCustomer
WHERE CountryRegionName = 'Australia';
GO
Did you find this helpful?
(1500 characters remaining)
Community Content Add
Annotations FAQ
Pay attention LEN doesn't count trailing spaces, DATALENGTH does

If you are writing CHECK constraints or data cleansing functions that scan for leading & trailing spaces. Becareful as LEN will cause you to overlook data containing trailing spaces whereas DATALENGTH will find them.

For Example: the following check constraint will prevent your users from adding data consisting of Leading or Trailing spaces or All Spaces.

    StateCode VARCHAR(3) NOT NULL CHECK (DATALENGTH(LTRIM(RTRIM(StateCode))) = DATALENGTH(StateCode)) -- Works

    StateCode VARCHAR(3) NOT NULL CHECK (LEN(LTRIM(RTRIM(StateCode))) = LEN(StateCode)) -- FAILS, as permits trailing spaces

Unfortunately neither work if you are checking for other whitespace eg: Tabs, due to limitations in SQL's TRIM functions.

History