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!
Like this:
Like Loading...
Related
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!
SQLZealots 
One thought on “LEN() and DATALENGTH() in SQL Server”