Differences and Similarities Between LEN and DATALENGTH Functions in SQL Server

Hi Friends,in this article i will explain about Differences  and Similarities Between LEN and DATALENGTH Functions in SQL Server.
I already explained in the previous articles about Common Table Expressions(CTE) in SQL SERVER 2008,Create ,list and Alter the trigger associated with a table with SQL Server? and SQL Server 2008 XML input parameter to stored procedure and insert XML data to Table


Len() function: 
Returns the number of characters of the specified string expression, excluding trailing blanks.

when using len() in a query to find out the length of a query, len() was not counting the trailing spaces in the value.

DATALENGTH():
Returns the number of bytes used to represent any expression. datalength() is counting the trailing spaces also.

DATALENGTH is especially useful with varchar, varbinary, text, image, nvarchar, and ntext data types because these data types can store variable-length data.

Similarities:

The LEN and DATALENGTH of NULL is NULL.

The return data type is BIGINT if the input expressions is of the VARCHAR(MAX), NVARCHAR(MAX) or VARBINARY(MAX) data types; otherwise the return data type is INT.

Differences:

LEN	DATALENGTH
Returns the number of characters of the specified string expression.	Returns the number of bytes used to represent any expression.
LEN is a string function.	DATALENGTH is a data type function.
Syntax: LEN ( string_expression )	Syntax: DATALENGTH ( expression )
Input parameter is of character or binary data type.	Input parameter is of any data type.
Excludes trailing blanks in determining the number of characters.	Includes trailing blanks in determining the number of bytes used.

Example 1:

DECLARE @FirstName VARCHAR(15) SET @FirstName='ASPDOTNET' SELECT LEN(@FirstName) AS LEN,         DATALENGTH(@FirstName) AS DATALENGTH

The output of the above code as shown in the below figure.

Example 2:

DECLARE @FirstName VARCHAR(15) SET @FirstName='ASPDOTNET   ' SELECT LEN(@FirstName) AS LEN,         DATALENGTH(@FirstName) AS DATALENGTH

The output of the above code as shown in the below figure.

"If you like my blog or articles, you can appreciate by leaving your comments or Liking my Facebook page Aspdotnet-kishore, following on Google+ Aspdotnet-Kishore, Twitter  on  AspdotnetKishore, Linked in Aspdotnet-Kishore, stumbling my posts on stumble upon and subscribing on  RSSfeed Aspdotnet-Kishore for free updates directly to your Email inbox . Watch my blog  for more articles."