LEN() vs DATALENGTH() in SQL Server – Easy and Clear!

The other day, one of my junior teammates asked me,
“What’s the difference between LEN() and DATALENGTH()?”
It was a good question — both are used to find the length of data, but they don’t work the same way.

Let me explain it in a simple and solid way.

📏 What is LEN()?

• LEN() tells you how many characters are in a string.

• It ignores any trailing spaces (spaces at the end).

• Perfect when you just want to know the number of characters the user can see.

✅ Example:

SELECT LEN('SQL Server   ')  -- Returns 10, skips the 3 spaces at the end

What is DATALENGTH()?

• DATALENGTH() tells you how many bytes SQL Server is actually using to store the data.

• It includes all spaces, even trailing ones.

• Very useful when you care about storage or working with binary data.

✅ Example:

SELECT DATALENGTH('SQL Server   ')  -- Returns 13, includes 3 spaces

 Summary

Conclusion

Even though both functions measure length, they do it in their own way:

• Use LEN() when counting characters.

• Use DATALENGTH() when dealing with storage size or precision.

So next time you’re working with SQL Server, just remember:
LEN() is for what you see, DATALENGTH() is for what SQL stores.