XML FeedsThe differences between LEN and DATALENGTH in SQL Server!
I have seen a bunch of questions about this recently and decided to do a little post to clear up the confusion.
First let's take a look what Books On Line has to say about these two functions
LEN
Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.
DATALENGTH
Returns the number of bytes used to represent any expression.
So what does that mean? It means that the LEN function will first right trim the value and then give you a count of the charaters, the DATALENGTH function on the other hand does not right trim the value and gives you the storage space required for the characters.
Take a look at this example
- declare @v nchar(5)
- select @v ='ABC '
- select len(@v),datalength(@v)
The output for len is 3 while the output for datalength =10. The reason that datalength returns the value 10 is because nvarchar uses 2 bytes to store 1 character by using unicode while varchar is using ascii which requires 1 byte per charaters
Let's take a look at some more data, first create this table
- create table #TeslLen ( CharCol char(5),
- VarCharCol varchar(5),
- NCharCol nchar(5),
- NVarCharCol nvarchar(5))
- insert #TeslLen values('A','A','A','A')
- insert #TeslLen values('AB','AB','AB','AB')
- insert #TeslLen values('ABC','ABC','ABC','ABC')
- insert #TeslLen values('ABCD','ABCD','ABCD','ABCD')
- insert #TeslLen values('ABCDE','ABCDE','ABCDE','ABCDE')
- insert #TeslLen values(' ',' ',' ',' ')
Now run the following query
- select CharCol as Value,len(CharCol) as LenChar,DATALENGTH(CharCol) as DLenChar,
- len(VarCharCol) as LenVarChar,DATALENGTH(VarCharCol)as DLenVarChar,
- len(NCharCol) as LenNChar,DATALENGTH(NCharCol) as DLenNChar,
- len(NVarCharCol) as LenNVarChar,DATALENGTH(NVarCharCol) as DLenNVarChar
- from #TeslLen
Here is the output for all the columns with LEN and DATALENGTH
| Value | LenChar | DatalengthChar | LenVarChar | DatalengthVarChar | LenNChar | DatalengthNChar | LenNVarChar | DatalengthNVarChar |
| A | 1 | 5 | 1 | 1 | 1 | 10 | 1 | 2 |
| AB | 2 | 5 | 2 | 2 | 2 | 10 | 2 | 4 |
| ABC | 3 | 5 | 3 | 3 | 3 | 10 | 3 | 6 |
| ABCD | 4 | 5 | 4 | 4 | 4 | 10 | 4 | 8 |
| ABCDE | 5 | 5 | 5 | 5 | 5 | 10 | 5 | 10 |
| 0 | 5 | 0 | 1 | 0 | 10 | 0 | 2 |
*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Excellent post. Another difference between Len and DataLength is that Len does not work for Text and nText columns, but DataLength does.
text? what is that :-)
its an good post.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.