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.

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