[ACCEPTED]-Len() vs datalength() in SQL Server 2005-datalength

Accepted answer

Score: 36

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

Score: 22

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

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

Here are the links to MSDN docs:

LEN()

DATALENGTH()

Score: 11

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

len does not count trailing spaces 3 either

take a look at this

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


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

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

Score: 2

Just use Replace():

SELECT LEN(REPLACE(N'4 Trailing Spaces:    ', ' ', '_'))

This will replace trailing 6 spaces with a character that LEN() will 5 actually count.

The problem with DataLength() is 4 you have to keep track of wether your string 3 is Unicode (nChar, nVarChar) or ASCII (Char, VarChar) to 2 know if you also need to divide the datalength 1 of a Unicode string by 2.

Score: 2

I was about to use the Len(Replace('blah 4 blah ',' ','_') suggestion when it struck 3 me it may be more efficient to use. Just 2 posting in case someone stumbles upon this 1 thread as I did.

len('blah blah ' + '.')-1

Score: 1

Unfortunately there is no perfect solution 14 that I am aware of.

One of the proposed 13 solutions, LEN(string + '.')-1 returns wrong results (-1) if 12 the string is Unicode of size 4000 or non-Unicode 11 and of size 8000. That is because the concatenation 10 is ignored. You can overcome this if you 9 want, by casting the string to a MAX-size 8 string: LEN(CAST(string as nvarchar(max)) + '.')-1, but is it worth it?

As mentioned 7 by others, DATALENGTH(string) returns the number of bytes 6 used for storage. For Unicode strings, it 5 may not be enough to divide the result by 4 2: Unicode surrogate characters can take more than 16 bits.

All in all, be 3 mindful of the limitations of each approach 2 and choose whatever you believe will cause 1 you less issues.

Source: stackoverflow.com

[ACCEPTED]-Len() vs datalength() in SQL Server 2005-datalength

SQL Server 2008 Empty String vs. Space

C# - SQLClient - Simplest INSERT

How do I add a Line break in a SQL Server Stored Procedure?

Real vs. Floating Point vs. Money

Stored Procedures to .sql files

Convert SELECT to an update statement

Why is SQL Server losing a millisecond?

How can I resolve my collation conflict in a better way?

SQL Script to alter ALL Foreign Keys to add ON DELETE CASCADE

How to list out all Foreign Keys with "WITH NOCHECK" in SQL Server