LEN and DATALENGTH with varbinary

varbinary data types store strings of bits. Binary constants have a leading 0x (a zero and the lowercase letter x) followed by the hexadecimal representation of the bit pattern, e.g. 0x2B1D. Both LEN and DATALENGTH can be used with varbinary datatype. However, they have some difference. If not used properly, you may not get valid results.

LEN

returns the number of characters of the specified string expression, excluding trailing blanks.

DATALENGTH

returns the number of bytes used to represent an expression.

 

DECLARE

@b varbinary(100);

SET

@b=0x42562A23

SELECT

LEN(@b), DATALENGTH(@b)

Both

returns 4.

DECLARE

@b varbinary(100);

SET

@b=0x42562A20

SELECT

LEN(@b), DATALENGTH(@b)

The

LEN returns 3 and DATALENGTH returns 4.

This is becuase

LEN converts the varinary to string first and then count the number of charater.

Run the following query

,

SELECT

CHAR(0x42), CHAR(0x56), CHAR(0x2A), CHAR(0x20)

We can

get the string that it represents is BV* (4 characters with the forth one is a trailing space).

Since

LEN ignores all trailling space, it returns 3. However, LEN does not ignore the trailing TAB, LF, and CR.

e

.g. for TAB (0x09):

DECLARE

@b varbinary(100);

SET

@b=0x42562A09

SELECT

LEN(@b), DATALENGTH(@b)

Both

returns 4.

So for the length of varbinary data type, it’s safe always to use DATALENGTH.

 

Advertisements
This entry was posted in SQL server. Bookmark the permalink.

One Response to LEN and DATALENGTH with varbinary

  1. Typo, dude, re-read article … carefully! 😉

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