Revert varbinary(max) data back after convert to varchar(max)

Question

I have a sql server column with varbinary(max). I converted it to varchar(max) for ssis package to move it to another table which is also varbinary(max). The value that was inserted is not correct and I want to revert back to the original value. So the original value was '0x504B03041400060008000000210094DE92' with SELECT convert(varchar(MAX),items,2) AS items, I got 0x35303442303330343134303030363030BB. The application is not working so I want to revert back to the original value. can anybody help me here.

Answer 1

But Viorel, you never have the incorrect value in your script...

Working from Viorel's script, here is an example how it may have happened, and how to repair it..

declare @original varbinary(max) = 0x504B03041400060008000000210094DE92
     declare @converted varchar(max) =  convert(varchar(max), @original, 2)
     declare @accident varbinary(max) = convert(varbinary(max), @converted)
     declare @revstring varchar(max) = convert(varchar(max), @accident)
     declare @revbinary varbinary(max) = convert(varbinary(max), @revstring, 2)

     select @original, @converted, @accident, @revstring, @revbinary

The key is that if you convert from varchar to binary without a style code, the characters will be copied bit by bit. That is the string '5' will be 0x35 - which is the binary representation of the character '5'. To convert to and from hexstrings you need the style codes 1 or 2.

Answer 2

This example seems to work:

declare @original varbinary(max) = 0x504B03041400060008000000210094DE92
declare @converted varchar(max) =  convert(varchar(max), @original, 2)
declare @reverted varbinary(max) = convert(varbinary(max), @converted, 2)

select @original, @converted, @reverted
--where @original = @reverted

Therefore, use convert to convert it back (maybe creating a new column, then removing the old one). But if the text starts with '0x', then try convert(varbinary(max), @converted, 1).

Answer 3

Hi
Sorry for the late response. The table that I am repairing is very huge. I have done the following steps and done just a few select statements and it seems to work by using your code above but I will mark it answer after I am done with everything.

1. have added a temp column to the table by doing--alter table items add tempCol varchar(max);
2. update items set tempCol = accident_data;
3. select top(1) convert(varbinary(max),tempCol, 2)
   from dbo.items ---> This gives me the original value back.
4. I am now going to update the table that I want to insert the correct values by update dbo.items set accident_column = null;
5. update dbo.items set accident_column = tempCol;

I am still working on it will post full results soon and mark your answer