In this article, I am going to explain you the LEN() function in SQL-Server with syntax and examples. The LEN() function in SQL-Server returns the number of characters in a given string expression. LEN() function does not include trailing blank spaces while calculating length.

Syntax

Below is the syntax for LEN() function in SQL-Server.

LEN ( string_expression )

Input Parameter

Input parameter is a string_expression. It can be a constant, variable or column of character or binary data.

Return Type

It returns bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types otherwise returns int.

Examples

select LEN('SQLServer') as 'CharCount'
CharCount : 9

select LEN('SQLServer  ') as 'CharCount'   --Here we have 2 blank spaces from trailing
CharCount : 9                              -- It does not count trailing spaces

select LEN('  SQLServer    ') as 'CharCount' --Here we have 2 blank spaces from Right and 4 from trailing
CharCount : 11                               -- It does not count trailing spaces

select LEN(NULL) as 'CharCount'      --When parameter is null, it returns null
CharCount : NULL

select LEN(' ') as 'CharCount'
CharCount : 0

select LEN('   ') as 'CharCount'
CharCount : 0

select Name, LEN(Name) as [Length] from tblEmployee
OUTPUT:
Name    Length
Rahul    5
Ravi     4
John     4

select Name, LEN(Name) as [Length] from tblEmployee where LEN(Name)OUTPUT:
Name    Length
Ravi     4
John     4

select Name, LEN(Name) as [Length] from tblEmployee where LEN(Name)>5 and LEN(Name)OUTPUT:
Name    Length
Yogesh  6

Data Type Support

It support char, varchar, nchar and nvarchar. It does not support text, ntext and image data types. Let's check with an examples. Here, we have a table variable and it has Name column of text data type.

declare @tblEmployee table(
ID int,
Name text
)

insert into @tblEmployee values(101,'Rahul')
insert into @tblEmployee values(102,'Ravi')

select LEN(Name) as 'Count' from  @tblEmployee

Msg 8116, Level 16, State 1, Line 8
Argument data type text is invalid for argument 1 of len function.

When input is nvarchar

declare @Name nvarchar(20) 
set @Name= 'Rahul'
select len(@Name) as 'CharCount'
CharCount : 5

declare @Name nvarchar(20) 
set @Name= 'Rahul   '       -- with trailing spaces
select len(@Name) as 'CharCount'
CharCount : 5

select LEN(N'Rahul') as 'CharCount'
CharCount : 5