THE SQL Server Blog Spot on the Web

Welcome to - 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

SET     @bin = 0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8


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



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)



--Sourced, but then modified, from Microsoft KB

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


if @IncludeSQL0xPrefix = 1

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


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



--SQL 2005 compatibility...  Yuk


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


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



RETURN @ReturnString


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

jayesh prajapat said:

Hi ,

I have a text file with text in Hindi language. I have saved in database in varbinary format. how to read text of that file.


is varbinary value

February 27, 2015 1:59 AM
New Comments to this post are disabled
Privacy Statement