Convert column data from Binary to String with SQL Server 2005

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.

About these ads

11 Responses

  1. You saved the day with this one. Nice quick solution!

  2. Nice one!

  3. Thanks mate, I’ve been searching everywhere for a solution like this!

  4. ditto…I was given all these complicated stored procedures and this was quick easy and painless.

    thanks much

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

  6. Great solution that saved my day as well!
    Thanks a lot!

  7. hello,
    i want the reverse of this,
    to convert from binary to string using sql server2005

  8. Worked great! I needed a quick text backup of all accounts on my site, and this did it.

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

  10. Thank you! Excellent solution – works perfectly!

  11. Wow thanks for the tip, I had a similar issue and was scouring the net for a solution. It really saved me a lot of time

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: