Microsoft, SQL Server, Technical

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:


The above function will in turn call


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.

12 thoughts on “Convert column data from Binary to String with SQL Server 2005

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

  2. Hi,
    Thanks for your solution.
    Is it possible to convert serialized object data stored by application(session binary data) as xml in sql server, without programming, only via sql?
    Best Regards.

  3. 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: Logo

You are commenting using your 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