ansaurus

Question

len() Vs datalength() in sqlserver 2005

Answer 1

+2 A:

Yes that's exactly what you must do. If you want to just get number of characters excluding blanks you would use LEN() function, while in all other cases DATALENGTH().

Even LEN() documentation has an information that to get number of bytes to represent the extension you should use DATALENGTH()

Here are the links to MSDN docs:

LEN()

DATALENGTH()

RaYell 2009-07-20 04:26:34

and LEN() does indeed **ALSO** count whitespaces.......

marc_s 2009-07-20 05:27:03

LEN(N'testing one two three, nothing but a test') returns 41 characters including whitespace (and DATALENGTH() on that returns 82)

marc_s 2009-07-20 05:27:35

@marc_s ... it doesn't count trailing whitespace though. LEN('HELLO'), LEN('HELLO ') and LEN(' HELLO') returns 5, 5 and 10 consecutively. This is documented ("").

Chris J 2009-07-23 10:41:15

Those examples were meant to have no leading/trailing whitespace, five spaces after HELLO and five spaces before HELLO. The joys of HTML collapsing whitespace :-)

Chris J 2009-07-23 10:42:18

Answer 2

+2 A:

Be careful. DATALENGTH returns the number of bytes used, not the number of characters.

Joe Chung 2009-07-20 04:28:33

+1 LEN on a NVARCHAR field returns the number of characters in the string, while DATALENGTH() returns twice that number (since NVARCHAR stores 2 bytes per character)

marc_s 2009-07-20 05:24:57

Answer 3

+2 A:

len counts the number of characters used not the storage required, this will be even more evident when you use nvarchar instead of varchar

len does not count trailing spaces either

take a look at this

declare @v nchar(5)
select @v ='ABC  '


select len(@v),datalength(@v)

and the output for len is 3 while the output for datalength =10

SQLMenace 2009-07-20 14:20:51

ansaurus

tags:

views:

answers:

len() Vs datalength() in sqlserver 2005

related questions