SQL Server LEN Function


By:

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]
len query results

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
len query results

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
len query results

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
len query results

Related Articles


Last Update: 11/3/2021




Comments For This Article





download














get free sql tips
agree to terms