May 162013
 

Both system functions, Len() and DataLength(), in SQL Server are used to measure the length of the data. The main difference of those 2 is that Len() gets the string length of the data in which DataLength measures the storage length of the data.

Len always converts the input to string and trim the ending, for instance

declare @x varchar(max), @x1 char(10), @x2 int
select @x = 'abc', @x1 = 'abc', @x2 = 999999
select len(@x) x, len(@x1) x1, len(@x2) x2
select datalength(@x) x, datalength(@x1) x1, datalength(@x2) x2
--Result
--x                    x1          x2
---------------------- ----------- -----------
--3                    3           6

--x                    x1          x2
---------------------- ----------- -----------
--3                    10          4

In this example x1 is an fixed length string. The ending spaces are trimmed by Len() but not trimmed by DataLength(). Some time this will cause some trouble if you mixed the concepts of those 2.

declare @x3 nvarchar(20), @x4 varbinary(20)
select @x3 = 'abc ' -- space at the end
select @x4 = 0x61626320 -- 4 byte long, abc + space
select len(@x3) x3, len(@x4) x4
select datalength(@x3) x3, datalength(@x4) x4
--Result
--x3          x4
------------- -----------
--3           3

--x3          x4
------------- -----------
--8           4

x3 is a string in unicode form. Every 2 bytes presents a character. Len() presents the “number of chars” of the string with trimmed ending spaces where DataLength() gives you 8, which is total storage of entire string.
x4 is a 4 byte binary. Len() converts it to string and trimmed the ending space, which is 0x20, and give you 3 where DataLength() give you the correct length of the data.
You can pass everything to DataLength(). A correct length of data in bytes will be returned, including sql_variant, CLR type, xml, etc…
But this is not the case for Len(). If the parameter cannot be explicitly converted to string, a weird error will be returned.(I think something like “Cannot implicitly convert…to string” will be more appropriate.)

declare @xml xml = '<a/>'
select len(@xml)
--Return
--Msg 8116, Level 16, State 1, Line 2
--Argument data type xml is invalid for argument 1 of len function.

John Huang, SQL Server MCM + MVP, http://sqlnotes.info

Share/Bookmark

  One Response to “Len() and DataLength()”

  1. Well, considering that LEN is a string function, it’s not all that surprising that you cannot use it on non-string arguments..

    Also a quick fix if you want to know the length of a string including the trailing spaces is to simple reverse the string before parsing it into the LEN function, as LEN only trims trailing spaces and not preceding spaces.

Add Comment Register



 Leave a Reply

(required)

(required)

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="">

Follow

Get every new post on this blog delivered to your Inbox.

Join other followers: