Convert Byte Array to string using TransactSQL

Question

Hi,

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

Answer 2

Why can't you just store the original string in a nvarchar(MAX) type column? If you have to use the varbinary type column, then at least use the System.Text.Encoding classes to do the conversion - then you can decode the strings in sql as well:

public static byte[] ToBytes(string value)
{
  if (value == null) return null;
  return System.Text.Encoding.Unicode.GetBytes(value);
}

And then use it like this:

select cast(VarBinaryField as nvarchar(max)) from SomeTable

Answer 3

I'd suggest using the method described http://msdn.microsoft.com/en-us/magazine/cc163473.aspx to use the .NET regex library. Specifically the section for CLR User Defined Functions. You could simply use the BinaryFormatter to deserialize your byte array.

[SqlFunction]
public static string ToStringFromBytes(byte[] value) 
{ if (value == null) return null;

  using (MemoryStream inMemoryData = new MemoryStream(value))
  {
    return new BinaryFormatter().Deserialize(inMemoryData) as string;
  }
}