THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Peter Larsson

Thinking outside the box

Convert binary value to string value

With SQL Server 2008, we can easily use

DECLARE @bin VARBINARY(MAX)
SET     @bin = 0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8

SELECT  CONVERT(VARCHAR(MAX), @bin, 2)


But how can we do this in SQL Server 2005?
You can't use a simple CAST or CONVERT, because you get the ASCII representation of the binary values.

So, here is how you can do the conversion with SQL Server 2005 by using XML.

-- Prepare value
DECLARE @bin VARBINARY(MAX)
SET     @bin = 0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8

-- Display the results
SELECT @bin AS OriginalValue,
        CAST('' AS XML).value('xs:hexBinary(sql:variable("@bin"))', 'VARCHAR(MAX)') AS ConvertedString

Published Wednesday, January 27, 2010 10:41 AM by Peso

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

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

January 28, 2010 1:10 PM
 

Ian Yates said:

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 :)

April 8, 2010 11:20 PM
 

Ian Yates said:

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!

April 8, 2010 11:33 PM
 

Lee said:

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

September 16, 2011 12:10 PM
 

Deepak said:

Thanks. It is great. helped me a lot.

November 21, 2011 4:41 AM
 

Danish said:

Great Man!!!

May 3, 2012 8:10 AM
 

Melbob said:

I have some guids in a binary(16) field, and get a string representation w-a-a-a-y faster than the XML approach by using string manipulation & SQL 2005's uniqueidentifier.  Not so "scrutable" sorry, but it might help someone:

declare @guid binary(16); select @guid = 0X03141593deadfacefeed98765432dcba;

select @guid, result = cast(cast(cast(reverse(substring(@guid,1,4))

+reverse(substring(@guid,5,2)) +reverse(substring(@guid,7,2)) as varbinary) +substring(@guid,9,8) as uniqueidentifier) as char(36))

June 4, 2014 9:02 PM
 

Peter said:

The CONVERT function deals with this real nice since 2008R2.

June 5, 2014 1:32 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement