When (data) length matters… SQL Server LEN vs DATALENGTH

Data Management - The differences between LEN and DATALENGTH in SQL Server

“The differences between LEN and DATALENGTH in SQL Server!
I have seen a bunch of questions about this recently and decided to do a little post to clear up the confusion.
First let's take a look what Books On Line has to say about these two functions

LEN
Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.

DATALENGTH
Returns the number of bytes used to represent any expression.

So what does that mean? It means that the LEN function will first right trim the value and then give you a count of the characters, the DATALENGTH function on the other hand does not right trim the value and gives you the storage space required for the characters.

Take a look at this example

…”

This has come up at work a few times (mostly the difference between LEN/DATALENGHT in relation to NVARCHAR’s) and I liked how this post displayed the differences…