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

Problem with saving to VARBINARY field Expand / Collapse
Author
Message
Posted Monday, September 17, 2012 7:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:31 PM
Points: 151, Visits: 391
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!
Post #1360518
Posted Monday, September 17, 2012 7:34 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:31 PM
Points: 151, Visits: 391
Ah
I figured out the solution after posting.

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


Thanks anyway Experts!
Post #1360520
Posted Monday, September 17, 2012 7:40 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 10:46 PM
Points: 194, Visits: 197
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
Post #1360521
Posted Tuesday, September 18, 2012 8:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:31 PM
Points: 151, Visits: 391
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!
Post #1360823
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse