SQL Server LEN Function
By: Daniel Calbimonte
The LEN function is used to provide the number of characters in a string without including trailing spaces. The function returns an integer of the value of characters and if the LEN is big for data types like nvarchar(max), varchar(max), the number returned is a bigint data type.
Syntax
LEN(expression)
Parameters
- expression - This is the string or expression used to measure the number of characters.
Simple LEN Example
Below is a simple example of the LEN function returning the number of characters of the names in the person table. This query uses the AdventureWorks sample database.
SELECT LEN(LastName) AS LENGTH FROM [Person].[Person]
Get List of Longest Strings from a Table Column
The following example will show the top 10 persons with the longest FirstNames.
SELECT TOP 10 LEN([FirstName]) AS LENGTH, FirstName FROM [Person].[Person] ORDER BY LEN([FirstName]) DESC
Example to Show that LEN Ignores Trailing Spaces
The trailing spaces are ignored by the LEN function. The following example illustrates that.
SELECT LEN('HELLO ') output
Example to Show that LEN Does Not Ignore Leading Spaces
In this example we can see the leading spaces are not ignored by the LEN function.
SELECT LEN(' HELLO ') output
