Post
Ask Question

Difference between Len() and DataLength() Function in SQL

Let us first understand in detail about these operators.

LEN() Function :

This function is a pre defined function in SQL Server. This function is used to calculate the length of the expression passed as input to the function excluding the trailing spaces.

Syntax

  1. LEN(string_expression)  
Let us use this function to see how it works. We have passed an input expression having the character length of 14. Let us see what output we get by executing the following script.
  1. DECLARE @input varchar(20)  
  2.   
  3. SET @input ='DotnetSnippets'  
  4.   
  5. SELECTLEN(@input)asLength  
output

We get the accurate output. Let us now add a trailing space to the input expression and see what the output is. Modify the script and execute.
  1. DECLARE @input varchar(20)  
  2.   
  3. SET @input ='DotnetSnippets '  
  4.   
  5. SELECTLEN(@input)asLength  
output

We get the same output. So Len function only returns the exact character length and does not include the trailing spaces. Let us now have a look at DataLength function in SQL.

DataLength() Function :

This function returns the number of bytes used or occupied by the data. It includes the trailing spaces also. Let us see a practical demo for the same.

Syntax
  1. DATALENGTH(string_expression)  
Let us use this function to see how it works. Execute the following script.
  1. DECLARE @input VARCHAR(20)  
  2. DECLARE @INPUT1 VARCHAR(20)  
  3. SET @input ='DotnetSnippets'  
  4. SET @INPUT1 ='DotnetSnippets '  
  5. SELECTDATALENGTH(@input)asLength  
  6. SELECTDATALENGTH(@INPUT1)as Length1  
Let us see the output of the preceding script.

output

We can we have two input strings. The first string has the expression without any trailing space. So it is equivalent to Len() function in this scenario. But when we add a trailing space to the input function it counts the space as well. So we get 15 as the count in case of DataLength() function.