LEN() vs DATALENGTH()

Posted: August 29, 2010 in SQL Server 2005

It is good to note the difference between these two functions so that the correct one can be used where applicable:

DATALENGTH() – Returns the number of bytes used to represent any expression. The key thing to note here is that it will include all bytes including spaces!

LEN() – Returns the number of characters of the specified string expression, excluding trailing blanks. The key thing to note here is that spaces will be ignored!

Run the following code to see the different values returned from each function:

DECLARE @liValue VARCHAR(10);
SELECT @liValue = '12345     '
SELECT @liValue [OriginalValue], LEN(@liValue) [ValueLen], DATALENGTH(@liValue) [ValueDataLength];
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s