tags:

views:

368

answers:

4

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.

+3  A: 

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.

Welbog
I knew I should have explained :) This is basically a bit field that has been "pivoted" and concatenated but without using a pivot (as I have to wrok within the constraints of 2000). I admit it looks crazy - but is there a way to do this anyway?
Kieran Benton
Whatever solution you end up up with, there had better be a wooden table in there somewhere.
TheTXI
@Kieran Benton: You're doing this in a roundabout way. You're converting from binary to string, then you want to convert from string to numeric then to hex string. You're better off converting directly from binary to numeric, that way you can output it in whatever base you want. You're building a string of numbers somehow. All you need to do is build an INT or BIGINT instead. Use addition and multiplication instead of concatenation.
Welbog
@welbog, can you elaborate please? This sounds like the way to go but I'm just missing the point slightly.
Kieran Benton
@Kieran Benton: You have two bits in your database at some point. You're concatenating them together to make a string. Instead of that, add and multiply them to make an integer. Instead of getting the string A + B, get the integer (2 * A) + B. It scales up better than strings, and is easier to convert to other bases since it's a real numeric type.
Welbog
A: 

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
AndyMcKenna
+1  A: 

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.

Remus Rusanu
A: 

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

Danny Shumer