ambiguous length varbinary variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

ambiguous length varbinary variable

Author

Topic

dalibor
Starting Member

21 Posts

Posted - 2008-06-24 : 04:56:23

Hi,

I'm trying simple code:

declare @varbinary varbinary(8000)
set @varbinary = 2592
print len( @varbinary )

number length
2590 4 bytes
2591 4 bytes
2592 3 bytes ????
2593 4 bytes

All number in range 1 - 9000 has length 4 bytes except these numbers:

32,288,544,800,1056,1312,1568,1824,2080,2336,2592,2848,3104,3360,3616,3872,4128,4384,4640,4896,5152,5408,5664,5920,6176,6432,6688,6944,7200,7456,7712,7968,8480,8736,8992

These numbers has length 3 bytes !

Why?

I´m using varbinary variable to transport ID values and parsing in loop

set @cnt = len( @binEmpIDList ) / 4 -- 4 length
....
insert into @List( ID ) values( substring( @varbinary, @i * 4 + 1, 4 ) )
...

but this algorithm malfunctioning in enumerated numbers.

Please help, how correct this.
ID values in @varbinary too much. I cannot using convert varbinary to bigint and convert bigint to string.

Dalibor

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-24 : 05:01:42

You need to use DATALENGTH() function to calculate length in terms of bytes.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

dalibor
Starting Member

21 Posts

Posted - 2008-06-24 : 05:11:53

quote:
Originally posted by harsh_athalye

You need to use DATALENGTH() function to calculate length in terms of bytes.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Thanks ! It is Working!
How simple!

Dalibor

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-24 : 05:15:37

Binary represenation is what the numbers you have found in common.

SELECT	Number,
	LEN(CAST(CAST(Number AS INT) AS VARBINARY(8000))) AS [LEN INT],
	DATALENGTH(CAST(CAST(Number AS INT) AS VARBINARY(8000))) AS [DATALENGTH INT],
	LEN(CAST(CAST(Number AS SMALLINT) AS VARBINARY(8000))) AS [LEN SMALLINT],
	DATALENGTH(CAST(CAST(Number AS SMALLINT) AS VARBINARY(8000))) AS [DATALENGTH SMALLINT]
FROM	master..spt_values
WHERE	Type = 'P'

E 12°55'05.25"
N 56°04'39.16"

dalibor
Starting Member

21 Posts

Posted - 2008-06-24 : 08:28:46

yes yes, my problem is solved, but remains question, why len numbered numbers (32,288,544,800...) is 3 bytes and no 4 bytes...
It is mystery for me....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-24 : 08:52:02

As I said before (yes I did), it is because of the binary representation!
Here are all the 36 numbers between 0 and 9000 that have a different LEN than DATALENGTH.

Number	Binary digits		VARBINARY	LEN	DATALENGTH
------	----------------	---------	---	----------
  32	00000000 00100000	0x00000020	3	4
 288	00000001 00100000	0x00000120	3	4
 544	00000010 00100000	0x00000220	3	4
 800	00000011 00100000	0x00000320	3	4
1056	00000100 00100000	0x00000420	3	4
1312	00000101 00100000	0x00000520	3	4
1568	00000110 00100000	0x00000620	3	4
1824	00000111 00100000	0x00000720	3	4
2080	00001000 00100000	0x00000820	3	4
2336	00001001 00100000	0x00000920	3	4
2592	00001010 00100000	0x00000A20	3	4
2848	00001011 00100000	0x00000B20	3	4
3104	00001100 00100000	0x00000C20	3	4
3360	00001101 00100000	0x00000D20	3	4
3616	00001110 00100000	0x00000E20	3	4
3872	00001111 00100000	0x00000F20	3	4
4128	00010000 00100000	0x00001020	3	4
4384	00010001 00100000	0x00001120	3	4
4640	00010010 00100000	0x00001220	3	4
4896	00010011 00100000	0x00001320	3	4
5152	00010100 00100000	0x00001420	3	4
5408	00010101 00100000	0x00001520	3	4
5664	00010110 00100000	0x00001620	3	4
5920	00010111 00100000	0x00001720	3	4
6176	00011000 00100000	0x00001820	3	4
6432	00011001 00100000	0x00001920	3	4
6688	00011010 00100000	0x00001A20	3	4
6944	00011011 00100000	0x00001B20	3	4
7200	00011100 00100000	0x00001C20	3	4
7456	00011101 00100000	0x00001D20	3	4
7712	00011110 00100000	0x00001E20	3	4
7968	00011111 00100000	0x00001F20	3	4
8224	00100000 00100000	0x00002020	2	4
8480	00100001 00100000	0x00002120	3	4
8736	00100010 00100000	0x00002220	3	4
8992	00100011 00100000	0x00002320	3	4

First column is the INT value, the second column is the binary value of the number and third column is the binary representation of the number.
The fourth column is the LEN and fifth column is the DATALENGTH.

When you later apply LEN to the binary value, SQL Server tries to convert the value to a string to calculate the length.
As you can see for ALL values above, they all ends with binary 0x20! And number 8224 even end with double 0x20 (0x2020).

Do you know what ascii character 0x20 is? Tadaa... It is space character.

And since LEN truncates trailing spaces, you get a smaller value for LEN than DATALENGTH which always counts all characters.

I hope this answer satisfy you.

E 12°55'05.25"
N 56°04'39.16"

dalibor
Starting Member

21 Posts

Posted - 2008-07-22 : 09:23:13

Yes it is satisfy answer.
double 0x20 .... len is 2 ....

Thanks !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-22 : 09:28:45

You're welcome.
Actually it is the reverse thing. Double 0x20 makes LEN deduct two characters.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources