I had this binary column in SQL Server 2005 which was storing the MD5 hashes of certain files. The requirement was to display these hashes in a ASP.NET web UI. So i set about the task of converting the same. Binary data as such cannot be displayed on the UI. It needs to be converted to String. So you could go ahead and read byte by byte and convert it into a string, concatenating it as you go along. To facilitate things, SQL Server comes built in with a function that could convert common data types to string. It is called:
sys.fn_sqlvarbasetostr
The above function will in turn call
sys.fn_varbintohexsubstring
in our case because we are trying to convert binary to string. To use the function in a select query just do so:
SELECT sys.fn_sqlvarbasetostr(MD5Hash) From HashTable
where MD5Hash is the binary column you would like to convert.
Filed under: Microsoft, SQL Server, Technical
You saved the day with this one. Nice quick solution!
Nice one!
Thanks mate, I’ve been searching everywhere for a solution like this!
ditto…I was given all these complicated stored procedures and this was quick easy and painless.
thanks much
Well I tried it and it didn’t work for me. The query still shows me binary data in that colum.
And yes, I used my own table and colum names in the query.
Great solution that saved my day as well!
Thanks a lot!
hello,
i want the reverse of this,
to convert from binary to string using sql server2005
Worked great! I needed a quick text backup of all accounts on my site, and this did it.
Hi,
Thanks for your solution.
Is it possible to convert serialized object data stored by asp.net application(session binary data) as xml in sql server, without programming, only via sql?
Best Regards.
Thank you! Excellent solution – works perfectly!