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 |