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!

One thought on “LEN() and DATALENGTH() in SQL Server”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s