Q:

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

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.

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.

