0

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.

Improve this question
8
  • is your only option in the database? and will this be on demand or run once? Commented Jul 2, 2009 at 17:34
  • 1
    Wouldn't... just returning the raw binary data be more efficient all around than this sort of contortionist manipulation? Commented Jul 2, 2009 at 17:44
  • its not - but it will be on demand and I'd like to keep the over the wire down to a minimum if possible. Commented Jul 2, 2009 at 17:44
  • 1
    @Kieran Benton: INTs are smaller than strings in any base. The string '094575E' takes up 7 bytes, but the integer 9721694 only takes up 4. Commented Jul 2, 2009 at 17:47
  • @Welbog: I understand that obviously, but I just didn't think in this particular case there was a possible way to go straight to a bigint. Commented Jul 2, 2009 at 17:48

4 Answers 4

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

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?
Whatever solution you end up up with, there had better be a wooden table in there somewhere.
@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, can you elaborate please? This sounds like the way to go but I'm just missing the point slightly.
@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.
1

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.

Improve this answer

Comments

0

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

Improve this answer

Comments

-1

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.