question

Benjoe-2576 avatar image
0 Votes"
Benjoe-2576 asked EchoLiu-msft commented

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

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.

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you have any update?Did the following methods solve your problem?If not, please provide more details.
If it is resolved,please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

0 Votes 0 ·

1 Answer

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered

Hi @Benjoe-2576

Someone wrote a function to convert varchar type to varbinary type, you can refer to:

 IF OBJECT_ID ('dbo.hexstr2varbin') IS NOT NULL
     DROP FUNCTION dbo.hexstr2varbin
 GO
    
 CREATE function [dbo].[hexstr2varbin](
   @char varchar(8000)
 )returns varbinary(8000)
 as
 begin
     declare @re varbinary(8000), @tempchar varchar(2), 
             @getchar varchar(1), @getint int, @n int, @totalint int,
             @i int, @tempint int, @runNum int 
    
      select @tempchar='',@i=datalength(@char), @re=0x; 
    
     if( @i>0)
     begin
         if ( @i%2 = 0) set @runNum= @i/2
         else set @runNum= @i/2 + 1 
    
         while (@runNum > 0)
         begin
             if(@runNum = 1) set @tempchar = @char 
             else set @tempchar = substring(@char, (@runNum-1)*2,2)
    
             select @n=1,@totalint=0;
                
                
             while @n < ( datalength(@tempchar) + 1 )
             begin
                 set @getchar=substring(@tempchar,@n,1);
    
            
                 select @getint=case  
                        when @getchar='a' then 10 
                        when @getchar='b' then 11
                        when @getchar='c' then 12 
                        when @getchar='d' then 13
                        when @getchar='e' then 14 
                        when @getchar='f' then 15
                        else  convert(int,@getchar) end;
    
                 set @tempint=@getint*power(16,datalength(@tempchar)-@n)
                 set @totalint = @totalint + @tempint
                 set @n=@n+1
             end 
    
                set @re=convert(varbinary(1),@totalint) + @re;    
                set @runNum=@runNum-1;         
         end
     end
    return @re
    
 end
 GO
 --test
 select dbo.hexstr2varbin('ffffff2353f5feefaffae1fff1ffbaf1faffa33333333ff3aaafffffffaaff')

If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.