Convert Byte Array to string using TransactSQL

Question

We are store string values in a database using varBinary type using c# and BinaryFormatter.We convert to byte array and then we save to DataBase

public static byte[] ToBytes(stringvalue)
{
  if (value == null)
    return null;

  byte[] inMemoryBytes;
  using (MemoryStream inMemoryData = new MemoryStream())
  {
    new BinaryFormatter().Serialize(inMemoryData, value);
    inMemoryBytes = inMemoryData.ToArray();
  }

  return inMemoryBytes;
}

OK, So if we save char "a", we can see "0x0001000000FFFFFFFF0100000000000000060100000001610B" in the database.After we can retrieve the data and convert again to string.
Can we convert this binary value ("0x0001000000FFFFFFFF0100000000000000060100000001610B") to char ("a") only using transact SQL (so we can do modifications, inserts, comparations from sql server console)?

Thanks a lot.

Answer 1

You could to use something simpler, as

Encoding.Default.GetBytes("a");

That will return "61" and can be more easily translated to a varchar, just running this:

create table #sample (content varbinary(max));
insert into  #sample values (0x61)
select cast(content as varchar) from #sample