len of varbinary

Question

hello,

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

Answer 1

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

Answer 2

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.

Answer 3

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)