len of varbinary

would someone please explain why select len(0x0a000b) returns开发者_高级运维 3? does len count bytes? and why select left(0x0a000b, 1) returns nothing? i expected 0x0a (if len counts bytes)...

i am using mssql 2005

thanks konstantin

select len(0x0a000b) is returning the length of a string represented by the three bytes 0x0a, 0x00, and 0x0b.

select left(0x0a000b, 1) returns the left-most character of the string, which is a newline character.

Note that select case when left(0x0a000b, 1) = 0x0a then 1 else 0 end returns 1, which indicates you are, indeed, getting the newline character.

Edit: Please see the comments below for additional details.

left is the string operator and so it doesn't work on binary data in the way you expect it to work, use substring(@v, 1, 1) instead

len, on the other hand, returns the length of binary data (apart of other data types); although, there's a very interesting exception to strings, when we measure the length of a string using this command it first performs rtrim on it; so len('a ') would return 1

would someone please explain why select len(0x0a000b) returns 3

it is 3 characters
0x means it is binary

0a character 1
00 character 2
0b character 3

you have a non printable character, I believe 0a is a carriage return, run this

select left(0x45000b, 1)