Thinking outside the box

Patron Saint of Lost Yaks

posts - 162, comments - 454, trackbacks - 0

My Links

SQLTeam.com Links

News

Search this Blog

Post Categories

<< Get date from ISO week number | Home | Invitation to startup meeting for PASS Scania >>

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

Print | posted on Wednesday, January 27, 2010 9:38 AM

Feedback

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

1/27/2010 11:23 AM | Transact Charlie

# re: Convert binary value to string value

Cool stuff, Peso!

1/27/2010 6:49 PM | Brad Schulz

# re: Convert binary value to string value

awesome

1/28/2010 7:28 AM | Vampal

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

2/2/2010 3:52 AM | Harsh Athalye