Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert varbinary to integers and insert Expand / Collapse
Author
Message
Posted Sunday, February 3, 2013 10:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 16, 2014 4:43 AM
Points: 4, Visits: 32
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)
Post #1415031
Posted Monday, February 4, 2013 9:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 13,139, Visits: 11,979
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


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:25 AM
Points: 1,426, Visits: 3,224
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
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:24 PM
Points: 955, Visits: 2,898
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)
...
)

Post #1415464
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse