Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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 | Filed Under [ SQL Server 2008 SQL Server 2005 ]

Feedback

Gravatar

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

# re: Convert binary value to string value

Cool stuff, Peso!
1/27/2010 6:49 PM | Brad Schulz
Gravatar

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

# 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.
4/20/2010 8:32 PM | frederic
Gravatar

# 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?
5/17/2010 7:35 AM | gioco al casinò gratis online
Gravatar

# re: Convert binary value to string value

Thanks
7/20/2010 1:34 PM | Sanjay Sonani
Gravatar

# re: Convert binary value to string value

Really nice post. With this way we can convert numbers easily.
8/18/2011 6:17 AM | Time tracking software
Gravatar

# re: Convert binary value to string value

ccc
7/31/2012 6:49 AM | s
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET