Tag: LEN() and DATALENGTH() in SQL Server

SQL

LEN() and DATALENGTH() in SQL Server
LEN() function returns the number of characters in the string excluding the trailing spaces, however, DATALENGTH() returns the bytes in the string that means it counts both trailing and leading spaces. Len() function return type is bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types; otherwise, int.

Let us quickly see the difference with a small set of data with various datatypes as below. 

Create table LENandDATALENGTH
(
	varcharString varchar(100),
	nvarcharString nvarchar(100),
	charString char(100),
	ncharString nchar(100)
)

Insert into LenandDatalength Values
('SQLServer','SQLServer','SQLServer','SQLServer'),--There is no leading or trailing space
(' SQLServer',' SQLServer',' SQLServer',' SQLServer'), -- There is leading space
('SQLServer ','SQLServer ','SQLServer ','SQLServer ') -- There is trailing space

Select *,
	Len(varcharString) varcharlen, Datalength(varcharString) varcharDatalen,
	Len(nvarcharString) nvarcharlen, Datalength(nvarcharString) nvarcharDatalen,
	Len(charString) charlen, Datalength(charString) charDatalen,
	Len(ncharString) ncharlen, Datalength(ncharString) ncharDatalen
From LenandDatalength

Drop table LenandDatalength

The below chart is arrived from the above results for a better understanding.

By now, we know the difference between LEN() and DATALENGTH() better, so use the functions as required to the right information as per the requirement.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!