Systems Engineering and RDBMS

Back to the basics: LEN() vs DATALENGTH()

Posted by decipherinfosys on October 18, 2007

In our back to basics series, today we will cover the difference between LEN() and DATALENGTH() functions to obtain the length of the column or a variable. LEN returns the number of character in the string or variable. DATALENGTH() returns the number of bytes used to represent any sting or expression. If string or variable value is NULL then both functions returns null. But there are certain basic differences between them.

If string contains trailing blanks then LEN() function does not take into account the trailing spaces and return the length only up to the last non null character. On the other hand, DATALENGTH() considers the total length including trailing spaces.  Example:

DECLARE @VAR1 VARCHAR(25)
SELECT @VAR1 = ‘Microsoft     ‘
SELECT @VAR1 AS COL_TEXT, LEN(@VAR1) AS LENGTH, DATALENGTH(@VAR1) AS DATA_LENGTH

Here is the output.

COL_TEXT                  LENGTH      DATA_LENGTH
------------------------- ----------- -----------
Microsoft                 9           14

Here DATALENGTH() gave us length as 14 characters while LEN() function did not considered the last five trailing spaces and returned the length as 9 characters.

When column or variable passed to the function are of the data type other than the string (numeric, datetime or int), both functions returns different value. DATALENGTH() function returns the internal length of the data type of the column or variable and LEN() function gives the actual value of the variable or expression. Here is an example for int and datetime data type variables.

DECLARE @VAR3 DATETIME
SELECT  @VAR3=GETDATE()
SELECT  @VAR3 AS COL_TEXT, LEN(@VAR3) AS LENGTH, DATALENGTH(@VAR3) AS DATA_LENGTH
GO
DECLARE @VAR2 INT
SELECT @VAR2 = 347456
SELECT @VAR2 AS COL_TEXT, LEN(@VAR2) AS LENGTH, DATALENGTH(@VAR2) AS DATA_LENGTH
GO

Output will be as shown below.

COL_TEXT                LENGTH      DATA_LENGTH
----------------------- ----------- -----------
2007-10-17 15:11:40.913 19          8
COL_TEXT    LENGTH      DATA_LENGTH
----------- ----------- -----------
347456      6           4

In above results, DATALENGTH() returned 8 & 4 respectively. These are the internal length of datetime data type and int data type respectively.  One can verify it by using sp_help command. Length shown by sp_help command will be same as displayed by DATALENGTH() function. But LEN() function returns the actual length of the string. It behaves same way for numeric data type columns as well. Based on precision and scale, DATALENGTH() may return different value, but LEN() will always return actual length (which includes decimal point as well). Following is the example followed by an output.

SELECT  ‘523643.6451’ AS COL_TEXT, LEN(523643.6451) AS LENGTH, DATALENGTH(523643.6451) AS DATA_LENGTH
UNION ALL
SELECT  ‘23643.645’ AS COL_TEXT, LEN(23643.645) AS LENGTH, DATALENGTH(23643.645) AS DATA_LENGTH
GO

COL_TEXT    LENGTH      DATA_LENGTH
----------- ----------- -----------
523643.6451 11          9
23643.645   9           5

Getting the length of the string or expression is very simple and can be achieved using LEN() or DATALENGTH() function, but we would like to draw an attention to the fact that there is a difference in behavior and using one in place of other may give different result.

About these ads

2 Responses to “Back to the basics: LEN() vs DATALENGTH()”

  1. […] And the answer would come out to be 9.  All we did was get the length of the string and then subtracted from it the length of the string after replacing all the empty spaces – we have to add 1 to account for the first word.  Also, note that we used the function len() instead of datalength() – you can read the difference between the two functions over here. […]

  2. […] between the functions: LEN() and DataLength() in SQL Server – you can access that post over here.  Got a question from a reader yesterday about calculating the length of the data in columns […]

Sorry, the comment form is closed at this time.

 
Follow

Get every new post delivered to your Inbox.

Join 85 other followers

%d bloggers like this: