Systems Engineering and RDBMS

Archive for October 18th, 2007

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.

Posted in SQL Server | 2 Comments »

 
Follow

Get every new post delivered to your Inbox.

Join 77 other followers