How can I convert a character-encoded binary string to hexadecimal in SQL Server?

Question

I'm trying to take a VARCHAR(MAX) with data in it as follows: "00001001010001010111010101..." etc.

Then encode it as hexadecimal for more efficient return to the client.

Is it possible to do this? Either directly or converting the string into a real binary column first before calling master.dbo.fn_varbintohexstr?

As an example, given the string:

0000100101000101011101011110

We should end up with:

0000 = 0
1001 = 9
0100 = 4
0101 = 5
0111 = 7
0101 = 5
1110 = E

094575E.

Or if there is an even more efficient method (reading binary directly?) then that would be even better. SQL Server 2000 compatible solutions are preferable.

Answer 1

Given your previous question, you're generating this string as part of another query. Why on Earth are you generating a string of ones and zeros when you can just multiply them by the appropriate power of 2 to make an INT out of them instead of a string? Converting from INT to hex string is trivial.

Answer 2

Are you looking for something like this: http://support.microsoft.com/kb/104829

Here it is in case the link ever dies:

    create procedure sp_hexadecimal
     @binvalue varbinary(255)
   as
   declare @charvalue varchar(255)
   declare @i int
   declare @length int
   declare @hexstring char(16)

   select @charvalue = '0x'
   select @i = 1
   select @length = datalength(@binvalue)
   select @hexstring = "0123456789abcdef"

   while (@i <= @length)
   begin

     declare @tempint int
     declare @firstint int
     declare @secondint int

     select @tempint = convert(int, substring(@binvalue,@i,1))
     select @firstint = floor(@tempint/16)
     select @secondint = @tempint - (@firstint*16)

     select @charvalue = @charvalue +
       substring(@hexstring, @firstint+1, 1) +
       substring(@hexstring, @secondint+1, 1)

     select @i = @i + 1

   end

   select 'sp_hexadecimal'=@charvalue

Answer 3

You can always split the string in 4 char groups (starting from end!), using SUBSTRING, and cast the 4 char groups to the hex digit, eg. '0011' to '3' in a big CASE statement. There are only 16 switch cases in the CASE, so is more than manageable. But all you get is a 4 times reduction in length, not sure if it worth the (significant) server overhead just to reduce the traffic.

Answer 4

An alternate solution might be by implementing a user defined function using your favorite .NET language