Peter Larsson Blog

Patron Saint of Lost Yaks

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

Legacy Comments


Transact Charlie
2010-01-27
re: Convert binary value to string value
Just to add that this is a future proofed alternative to using the undocumented function call
[code]sys.fn_sqlvarbasetostr[/code]
which you may have come across on other sites if you were searching for a way to do this.

Brad Schulz
2010-01-27
re: Convert binary value to string value
Cool stuff, Peso!

Harsh Athalye
2010-02-02
re: Convert binary value to string value
This is really cool and quick way to convert binary to string. Another would be to use undocumented function fn_varbintohexstr()

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

SELECT master.dbo.fn_varbintohexstr(@bin)

frederic
2010-04-20
re: Convert binary value to string value
The function fn_varbintohexstr provided with SQL Server do not work for size over 1999. The XML solution works fine for all lengths of binary data.

gioco al casinò gratis online
2010-05-17
re: Convert binary value to string value
I use a FF extension called FEBE and dropbox to backup bookmarks, FF extensions, etc. This also allows me to sync across several different environments. Thanks for posting some great ideas and I'll try to return back with a completely different browser to check things out! Also, I put a link to your blog at my site, hope you don't mind?

Sanjay Sonani
2010-07-20
re: Convert binary value to string value
Thanks

Time tracking software
2011-08-18
re: Convert binary value to string value
Really nice post. With this way we can convert numbers easily.

s
2012-07-31
re: Convert binary value to string value
ccc