SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problem with saving to VARBINARY field


Problem with saving to VARBINARY field

Author
Message
haiao2000
haiao2000
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 438
Dear Expert,
I have a wierd requirements which I have been struggling for awhile now. here is the code

declare @Value2Convert varchar(max)
declare @temp table( binaryField varbinary(max))
declare @result varchar(max)
select @Value2Convert= 13, @result = ''

while 1=1
begin
select @result= '0'+convert(char(1),@Value2Convert % 2)+@result, @Value2Convert = convert(int, (@Value2Convert / 2))
if @Value2Convert = 0 break
end

--this return value desired value, I want to save in varbinary field the way it is
select '0x'+ @result

--this will not work as it will return error
--insert into @temp(binaryField)
--select @result

--this will work but result is incorrect as 0x01010001 will be converted to 0x3031303130303031
insert into @temp(binaryField)
select cast(@result as varbinary(max))

select * from @temp



if the binaryField is varchar type then there wont be a problem, but I was told it must be varbinary. any way to work around this issue?

Thanks in advance!
haiao2000
haiao2000
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 438
Ah
I figured out the solution after posting.

insert into @temp(binaryField)
select convert(varbinary(max), @result , 2)


Thanks anyway Experts! :-)
ultimate sql
ultimate sql
SSC Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 Visits: 256
Yes, that's correct. :-)

And if you want to keep the data in default varbinary format and display it in character when selecting then last statement can be changed to display in varchar e.g.

select cast(binaryField as varchar(max)) from @temp
select convert(varchar(max), binaryField, 0) from @temp





Fahim Ahmed
Knowledge is a journey, not a destiny
haiao2000
haiao2000
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 438
ultimate sql (9/17/2012)
Yes, that's correct. :-)

And if you want to keep the data in default varbinary format and display it in character when selecting then last statement can be changed to display in varchar e.g.

select cast(binaryField as varchar(max)) from @temp
select convert(varchar(max), binaryField, 0) from @temp





Thank you!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search