September 17, 2012 at 7:05 pm
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!
September 17, 2012 at 7:34 pm
Ah
I figured out the solution after posting.
insert into @temp(binaryField)
select convert(varbinary(max), @result , 2)
Thanks anyway Experts! 🙂
September 17, 2012 at 7:40 pm
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
September 18, 2012 at 8:28 am
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 @tempselect convert(varchar(max), binaryField, 0) from @temp
Thank you!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply