Overview
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.
Explanation
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
Additional Information
Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs.
- MSSQLTips Awards: Author of the Year Contender – 2015-2018, 2022, 2023 | Champion (100+ tips) – 2018
