How to get the original binary value back to string

Question

My SQL query returns the value as below

Declare @ID varbinary(18)
Declare @newReturnID NvarCHAR(100) 
set @ID =  CONVERT(VARBINARY(18), CONVERT(VARCHAR(2), CONVERT(VARBINARY(2), 41)) +  CONVERT(VARCHAR(64), NEWID())) 
select  @ID ID

ID(Column name)

0x002936354446393642302D333936312D3436

I need to assign an variable as the above value i am trying to convert into an string but i get an different value ?

foreach (DataRow dr in dt.Rows)
{
    byte[] bytes = (byte[])dr["ID"];
    string strID = Convert.ToBase64String(bytes, 0, bytes.Length);
}

By my string strID holds different value, not as the above one?

Let me know where I am going wrong?

Inside sql you see base-16 string, but inside c# you are reading byte array from db and converting to base-64 string. Why would you expect them to be the same? — SᴇM, May 30 '18 at 08:22
First of all, you are using `NEWID()` in your SQL statement, thus you always create a different value. Second, in your C# you are converting to a base64 encoded string. Thus you will never get an identical result for the source string and the base64 encoded string. Third, you seem to have converted a different result when you gave your `strID`. The actual base64 value for `0x002936354446393642302D333936312D3436` would be `ACk2NURGOTZCMC0zOTYxLTQ2`. Fourth, your varbinary starts with 0x00, thus converting it to a string would result in the string starting with the `\0` terminator character. — Adwaenyth, May 30 '18 at 08:56
i changed it in sql to send data as varchar :select convert(varchar(max), @ID, 1) as ID — Kumee, May 30 '18 at 10:18

par · Accepted Answer · 2018-05-30T09:13:32.743

You are converting Bytes to Base64 string while 0x002.. is hex representation fo bytes.

If it's not compulsory to convert string in c# code, try select converting string in SQL select statement.

select convert(varchar(max), @ID, 1) as  ID

Refer this link (Binary Style) for detail for 3rd argument in Convert.

Edit:

Also, in c# you will need to convert byte array to hex string. here is answer already available for this to convert byte array to hex string. One function posting from that link.

public static string ByteArrayToString(byte[] ba)
{
  StringBuilder hex = new StringBuilder(ba.Length * 2);
  foreach (byte b in ba)
    hex.AppendFormat("{0:x2}", b);
  return hex.ToString();
}

How to get the original binary value back to string

1 Answers1