What is the Difference between Len() and DataLength() Functions in SQL

SQL Server Len() Function Vs. DataLength() Function  The LEN system function returns the number of characters of the specified string expression.   On the other hand, the DATALENGTH function returns the number of bytes used to represent any expression.  Here’s a summary of the similarities and differences between the LEN() and DATALENGTH() system functions: 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. Excludes trailing blanks in determining the number of characters.  Includes trailing blanks in determining the          number of bytes used. Input parameter is of character or binary data type.  Input parameter is of any data type. LEN is a string function.  DATALENGTH is a data type function. LEN() returns the number of characters in the string or variable while DATALENGTH() returns the number of bytes used to represent a string or expression. We have 4 different cases in which we can study  these two functions. Case 1:  With  "Varchar" Data types:  When a  Varchar type is passed to both the DATALENGTH() and LEN() functions, they return the same value. Example:                 DECLARE @Value varchar(20)                         SET @value = ‘Reddyz’                 SELECT  DATALENGTH(@value)                 SELECT  LEN(@value) These statements both return 6 as the Varchar type uses 6 single bytes characters to store the 6-character value. So in this case, both the functions are returning the same. Case 2:   With "Nvarchar" Data types:    If an nvarchar type is used, DATALENGTH() takes twice as many bytes to manage a value of the same length whereas LEN() returns the number of characters. Example:                 DECLARE @Value nvarchar(20)                        SET @value = ‘Reddyz’                 SELECT  DATALENGTH(@value)                 SELECT LEN(@value) The DATALENGTH() returns 12 because 2 bytes are used to store each character using a Unicode character set.  The LEN() returns 6 as this returns the number of  characters,  not the number of bytes.

Case 3:   With Integer Data types:  If integer Datatype is used, DATALENGTH()  returns 4 and LEN() returns the number of digits. Example:                 DECLARE  @Value1 int, @value2 int                         SET @value1=2                         SET @value2 =20000                 SELECT  DATALENGTH(@value1)                 SELECT  LEN(@value1)                 SELECT  DATALENGTH(@value2)                 SELECT  LEN(@value2) The DATALENGTH()  returns 4 in both the cases because int always takes 4 bytes, whatever the value would be. But the LEN() treats the integer value as if it were converted to a character type and  returns the number of digits. So results are 1 and 5 respectively. Case 4:  With Extra Spaces: If string or variable value is NULL then both functions returns null. If  the string is having trailing blanks then LEN() function returns the length only up to the last   non null character and ignores the spaces. On the other hand, DATALENGTH() behaves differently returning all  along with spaces. Example:                   DECLARE @value1 VARCHAR(25)                   SELECT @value1 = 'Reddyz     '                   SELECT @value1 AS COL_TEXT, LEN(@value1) AS   LENGTH, DATALENGTH(@value1) AS   DATA_LENGTH The result would be 6 and 11. As DATALENTH() takes into account the spaces , so it returns 11 and LEN() returns only the non-null characters so it returns 6.