Convert Byte Array to string using TransactSQL
Solution 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
Solution 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;
}
}
Oscar
Updated on June 09, 2022Comments
-
Oscar almost 2 years
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.