Convert binary value to string value

There's also, FYI, an undocumented function, fn_varbintohexstr. But probably better to use the XML approach.

I came across the same issue a few weeks ago dealing with binary SIDs in active directory linked server queries (which is a painful thing to do!).

I had been reading BOL for SQL 2008 and was testing against SQL 2005 and 2008.  Oddly enough, 2005 accepts the 2008 syntax without error but, as you said, doesn't do anything special.

This is the function I came up with (using code borrowed from a Microsoft KB article)

ALTER function [dbo].[BinToHex](

@BinValue varbinary(max),

@IncludeSQL0xPrefix bit = 1

)

RETURNS varchar(max)

as

BEGIN

--Sourced, but then modified, from Microsoft KB http://support.microsoft.com/kb/104829

--On SQL Server 2008 this is MUCH easier as shown below

declare @ReturnString varchar(max)

--Running on 2008 or better?  Product version is 10.0 or better.  2005 is 9.x

if dbo.SQLMajorVersion() >= 10

begin

if @IncludeSQL0xPrefix = 1

SET @ReturnString = CONVERT( varchar(max), @BinValue, 1 )  --style #1 prefixes with 0x

else

SET @ReturnString = CONVERT( varchar(max), @BinValue, 2 )  --style #2 DOES NOT prefix with 0x

end

ELSE

--SQL 2005 compatibility...  Yuk

begin

--declare @charvalue varchar(255)

declare @i int

declare @length int

declare @hexstring char(16)

select @ReturnString = case when @IncludeSQL0xPrefix = 1 then '0x' else '' end,

@i = 1,

@length = datalength(@binvalue),

@hexstring = '0123456789abcdef'

while (@i <= @length)

begin

declare @tempint int

declare @firstint int

declare @secondint int

set @tempint = convert(int, substring(@binvalue,@i,1))

set @firstint = floor(@tempint/16)

set @secondint = @tempint - (@firstint*16)

set @ReturnString = @ReturnString +

substring(@hexstring, @firstint+1, 1) +

substring(@hexstring, @secondint+1, 1)

set @i = @i + 1

end

end

RETURN @ReturnString

END

This way I can have one set of code that supports both servers.  I needed this function because, as described above, I was doing LDAP queries which require (as far I as know) openQuery.  To make the queries dynamic I need to use ugly dynamic SQL with double quoting, etc...

I'll look in to the XML approach since it seems much more elegant and doesn't do lots of loop iterations.  I'm only using it for a handful of rows and each binary field is <100 bytes so it's not a big deal at the moment but it's nice to get an efficient process at the beginning in case it's repurposed without further thought for performance down th eline.

Thanks for posting!  I've just added your blog to my feeds list :)

Just did some very rudimentary testing on my local SQL 2005 instance.  For 10000 iterations the long UDF I posted took around 1600ms.  Putting your XML conversion in that UDF (so I'm comparing apples with apples for UDF overheads) drops the time down to 1100ms.  

Without the UDF wrapper it runs much faster taking ~670ms.

On SQL 2008 the CONVERT function does it between 20-30ms :)

We've only dropped support for SQL 2000 in the past year or so (finally!) and we try to ensure all new installations are on SQL 2008.  I'd love to be able to use new features like MERGE sprinkled throughout our stored procs and import/export scripts one day!

Thank you for posting this.  It is exactly what I needed!

Thanks. It is great. helped me a lot.

Great Man!!!