February 3, 2013 at 10:27 am
Hi,
I have a 28 integers stored in a varbinary that is passed to a stored procedure. The SP needs to parse the varbinary and insert the integers into a table, column 5, column6, ..., column 32. So far I have a brute force method but would prefer something a bit more elegant, like a loop?
I have limited SQL experience and don’t know the best / most efficient methods.
Thank you,
Jonathan
set @pos=1
set @b1=substring(@block,@pos,1)
set @b2=substring(@block,@pos+1,1)
set @b3=substring(@block,@pos+2,1)
set @b4=substring(@block,@pos+3,1)
set @int0=convert(int,(@b4+@b3+@b2+@b1))
set @pos=@pos+4
set @b1=substring(@block,@pos,1)
set @b2=substring(@block,@pos+1,1)
set @b3=substring(@block,@pos+2,1)
set @b4=substring(@block,@pos+3,1)
set @int1=convert(int,(@b4+@b3+@b2+@b1))
....
set @pos=@pos+4
set @b1=substring(@block,@pos,1)
set @b2=substring(@block,@pos+1,1)
set @b3=substring(@block,@pos+2,1)
set @b4=substring(@block,@pos+3,1)
set @int27=convert(int,(@b4+@b3+@b2+@b1))
insert tbl_LotData(lotnumber, StartDTS, EndDTS,LotQuantity
,block0,block1,block2,block3,block4,block5,block6
,block7,block8,block9,block10,block11,block12
,block13,block14,block15,block16,block17,block18
,block19,block20,block21,block22,block23,block24
,block25,block26,block27)
values (@lotnumber, @startDTS,@endDTS,@LotQuantity,
@int0,@int1,@int2,@int3,@int4,@int5,@int6,@int7
,@int8,@int9,@int10,@int11,@int12,@int13,@int14,@int15
,@int16,@int17,@int18,@int19,@int20,@int21,@int22,@int23
,@int24,@int25,@int26,@int27)
February 4, 2013 at 9:09 am
I am having a hard time understanding what you are trying to do here. If you can post what the input looks like and the output should look like I am sure we can help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 4, 2013 at 11:11 am
Your brute force method can't possibly be giving you the correct int result.
Given that each byte of the varbinary is 8 bits of an integer you would have to multiple each one by some factor of 2 before adding them together. Even then you would need to know if the varbinary representation is big-endian or little endian.
The probability of survival is inversely proportional to the angle of arrival.
February 4, 2013 at 12:53 pm
Hi
Not sure if this will help, but the following example packs a couple of varbinarys with 4 integers in both endians (assuming 4 bytes per integer). Then unpacks them. You shouldn't need to nibble through the binary byte by byte.
declare @i1 int = 1234
declare @i2 int = 5678
declare @i3 int = 9101
declare @i4 int = 1121
declare @vb1 varbinary(16)
declare @vb2 varbinary(16)
set @vb1 = CAST(@i1 as varbinary(16))
set @vb1 = @vb1 + CAST(@i2 as varbinary(16))
set @vb1 = @vb1 + CAST(@i3 as varbinary(16))
set @vb1 = @vb1 + CAST(@i4 as varbinary(16))
set @vb2 = CAST(REVERSE(CAST(@i1 as varbinary(16))) as varbinary(16))
set @vb2 = @vb2 + CAST(REVERSE(CAST(@i2 as varbinary(16))) as varbinary(16))
set @vb2 = @vb2 + CAST(REVERSE(CAST(@i3 as varbinary(16))) as varbinary(16))
set @vb2 = @vb2 + CAST(REVERSE(CAST(@i4 as varbinary(16))) as varbinary(16))
SELECT @vb1
SELECT @vb2
SELECT cast(substring(@vb1,1,4) as int)
, cast(substring(@vb1,5,4) as int)
, cast(substring(@vb1,9,4) as int)
, cast(substring(@vb1,13,4) as int)
SELECT cast(cast(reverse(substring(@vb2,1,4)) as varbinary(4)) as int)
, cast(cast(reverse(substring(@vb2,5,4)) as varbinary(4)) as int)
, cast(cast(reverse(substring(@vb2,9,4)) as varbinary(4)) as int)
, cast(cast(reverse(substring(@vb2,13,4)) as varbinary(4)) as int)
So applying that to your insert and reversing it as in your example, would give us the following
insert tbl_LotData(lotnumber, StartDTS, EndDTS,LotQuantity
,block0
,block1
...
)
values (@lotnumber, @startDTS,@endDTS,@LotQuantity,
cast(cast(reverse(substring(@block,1,4)) as varbinary(4)) as int)
, cast(cast(reverse(substring(@block,5,4)) as varbinary(4)) as int)
...
)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply