Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Convert varbinary to integers and insert Rate Topic Display Mode Topic Options
Author
 Message
 Posted Sunday, February 3, 2013 10:27 AM
 Forum Newbie Group: General Forum Members Last Login: Saturday, October 31, 2015 11:49 AM Points: 4, Visits: 38
 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,Jonathanset @pos=1set @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+4set @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+4set @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)
Post #1415031
 Posted Monday, February 4, 2013 9:09 AM
 SSCoach Group: General Forum Members Last Login: 2 days ago @ 8:08 PM Points: 16,145, Visits: 16,850
 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 Moden's splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)
Post #1415367
 Posted Monday, February 4, 2013 11:11 AM
 UDP Broadcaster Group: General Forum Members Last Login: Wednesday, October 19, 2016 12:41 PM Points: 1,447, Visits: 3,254
 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.
Post #1415416
 Posted Monday, February 4, 2013 12:53 PM
 Ten Centuries Group: General Forum Members Last Login: Monday, November 28, 2016 11:55 AM Points: 1,227, Visits: 3,308
 HiNot 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 = 1234declare @i2 int = 5678declare @i3 int = 9101declare @i4 int = 1121declare @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 @vb1SELECT @vb2SELECT 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)...)`
Post #1415464

 Permissions