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.

Rubens Farias · Accepted Answer · 2010-01-25 13:23:41Z

4

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

answered Jan 25, 2010 at 13:23

Rubens Farias

58k8 gold badges136 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Oscar Over a year ago

Thanks Rubens, but this tecnique is used for another framework (Entlib Contrib entlibcontrib.codeplex.com) and cant change it. They use it because then thay can store more types:ints,string,images etc etc and we want (only when the type is string) convert this stored values to string

Rubens Farias Over a year ago

Oscar, can BitConverter.GetBytes be useful?

Oscar Over a year ago

but this is a method from the .Net framework,isnt it? I want to use only SqlConsole. I´m programming it using CLR Sql functions, according Kleinux. Thanks again.

Kleinux · Accepted Answer · 2010-01-25 13:29:39Z

2

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;
  }
}

answered Jan 25, 2010 at 13:29

Kleinux

1,54111 silver badges22 bronze badges

1 Comment

Oscar Over a year ago

You are rigth, I didnt remember about it... I going to see. Thanks!!

Pent Ploompuu · Accepted Answer · 2010-01-25 13:24:37Z

0

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

answered Jan 25, 2010 at 13:24

Pent Ploompuu

5,4041 gold badge31 silver badges48 bronze badges

2 Comments

Oscar Over a year ago

Thanks Pent, but this tecnique is used for another framework (Entlib Contrib entlibcontrib.codeplex.com) and cant change it. They use it because then thay can store more types:ints,string,images etc etc and we want (only when the type is string) convert this stored values to string

Pent Ploompuu Over a year ago

Then you could use CLR user defined functions as suggested by Kleinux.

Collectives™ on Stack Overflow

Convert Byte Array to string using TransactSQL

3 Answers 3

3 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related