Difference Between LEN and DATALENGTH Functions in SQL Server

Introduction:

In this article I will explain differences between Len() and DataLength() functions in sql server.

Description:

In previous post I explained differences between char, varchar and nvarchar, difference between tinyint,smallint,int,bigint and many articles relating to SQL Server. Now I will explain differences between Len() and DataLength() functions in SQL Server.

Len() function

This Len() function will return number of characters in the string expression excluding the trailing blanks. This mean it will right trim the blank values and give you the count of characters.

DataLength() function

This DataLength() function will return number of bytes to represent any expression. This mean it will give you a storage space required for the characters.

Check below examples for Len() and DataLength() functions

Example1:

In this example I am taking string value without spaces

DECLARE @str VARCHAR(50) SET @str='Suresh' SELECT LEN(@str) AS LenCount SELECT DATALENGTH(@str) AS DataLengthCount

OutPut

Example2:

In this example I am taking string value with spaces

DECLARE @str VARCHAR(50) SET @str='Suresh   ' SELECT LEN(@str) AS LenCount SELECT DATALENGTH(@str) AS DataLengthCount

OutPut

If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS Subscribe by Email