Convert Varbinary to Varchar in select statement

Question

I have a column in table that contains huge chunks of varbinary data. I need to convert this data to normal english(string) while doing a select statement. I have tried the following methods and so far none has worked for me

declare @test varbinary(max) = 0x0001000000FFFFFFFF01000000000000000C020000005A4562697845786368616E67652E436F6E646974696F6E732C2056657273696F6E3D312E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D343765373638633534313730633631390501000000274562697845786368616E67652E436F6E646974696F6E732E436F6E646974696F6E7347726F757004000000084F70657261746F72144368696C64436F6E646974696F6E73436F756E74114368696C64436F6E646974696F6E735F30104368696C6447726F757073436F756E74040004002F4562697845786368616E67652E436F6E646974696F6E732E436F6E646974696F6E7347726F75704F70657261746F720200000008214562697845786368616E67652E436F6E646974696F6E732E436F6E646974696F6E02000000080200000005FDFFFFFF2F4562697845786368616E67652E436F6E646974696F6E732E436F6E646974696F6E7347726F75704F70657261746F72010000000776616C75655F5F00080200000001000000010000000904000000000000000C050000005A4562697845786368616E67652E4E616D65644974656D732C2056657273696F6E3D312E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D343765373638633534313730633631390504000000214562697845786368616E67652E436F6E646974696F6E732E436F6E646974696F6E03000000084F70657261746F720E4C65667445787072657373696F6E10526967687445787072657373696F6E73040406294562697845786368616E67652E436F6E646974696F6E732E436F6E646974696F6E4F70657261746F7202000000214562697845786368616E67652E4E616D65644974656D732E4E616D65644974656D050000000200000005FAFFFFFF294562697845786368616E67652E436F6E646974696F6E732E436F6E646974696F6E4F70657261746F72010000000776616C75655F5F00080200000001000000090700000009080000000507000000214562697845786368616E67652E4E616D65644974656D732E4E616D65644974656D0100000009506174684974656D73042B4562697845786368616E67652E4E616D65644974656D732E4E616D65644974656D506174684974656D5B5D05000000050000000909000000110800000001000000060A0000000454657374070900000000010000000300000004294562697845786368616E67652E4E616D65644974656D732E4E616D65644974656D506174684974656D0500000005F5FFFFFF294562697845786368616E67652E4E616D65644974656D732E4E616D65644974656D506174684974656D03000000084974656D4E616D650C4E756D65726963496E6465780B537472696E67496E6465780100010805000000060C0000000B5472616E73616374696F6EFFFFFFFF0A01F3FFFFFFF5FFFFFF060E0000000A506172616D6574657273FFFFFFFF060F000000045465737401F0FFFFFFF5FFFFFF06110000000556616C7565FFFFFFFF0A0B
select CAST(@test as varchar(max))
select CONVERT(varchar(max), @test, 2)
select master.dbo.fn_varbintohexstr(@test)

the first statement returned nothing, 2nd returned

0001000000FFFFFFFF01000000000000000C020000005A4562697845786368616E67652E436F6E646974696F6E732C2056657273696F6E3D312E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D343765373638633534313730633631390501000000274562697845786368616E67652E436F6E646974696F6E732E436F6E646974696F6E7347726F757004000000084F70657261746F72144368696C64436F6E646974696F6E73436F756E74114368696C64436F6E646974696F6E735F30104368696C6447726F757073436F756E74040004002F4562697845786368616E67652E436F6E646974696F6E732E436F6E646974696F6E7347726F75704F70657261746F720200000008214562697845786368616E67652E436F6E646974696F6E732E436F6E646974696F6E02000000080200000005FDFFFFFF2F4562697845786368616E67652E436F6E646974696F6E732E436F6E646974696F6E7347726F75704F70657261746F72010000000776616C75655F5F00080200000001000000010000000904000000000000000C050000005A4562697845786368616E67652E4E616D65644974656D732C2056657273696F6E3D312E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D343765373638633534313730633631390504000000214562697845786368616E67652E436F6E646974696F6E732E436F6E646974696F6E03000000084F70657261746F720E4C65667445787072657373696F6E10526967687445787072657373696F6E73040406294562697845786368616E67652E436F6E646974696F6E732E436F6E646974696F6E4F70657261746F7202000000214562697845786368616E67652E4E616D65644974656D732E4E616D65644974656D050000000200000005FAFFFFFF294562697845786368616E67652E436F6E646974696F6E732E436F6E646974696F6E4F70657261746F72010000000776616C75655F5F00080200000001000000090700000009080000000507000000214562697845786368616E67652E4E616D65644974656D732E4E616D65644974656D0100000009506174684974656D73042B4562697845786368616E67652E4E616D65644974656D732E4E616D65644974656D506174684974656D5B5D05000000050000000909000000110800000001000000060A0000000454657374070900000000010000000300000004294562697845786368616E67652E4E616D65644974656D732E4E616D65644974656D506174684974656D0500000005F5FFFFFF294562697845786368616E67652E4E616D65644974656D732E4E616D65644974656D506174684974656D03000000084974656D4E616D650C4E756D65726963496E6465780B537472696E67496E6465780100010805000000060C0000000B5472616E73616374696F6EFFFFFFFF0A01F3FFFFFFF5FFFFFF060E0000000A506172616D6574657273FFFFFFFF060F000000045465737401F0FFFFFFF5FFFFFF06110000000556616C7565FFFFFFFF0A0B

and the 3rd returned

0x0001000000ffffffff01000000000000000c020000005a4562697845786368616e67652e436f6e646974696f6e732c2056657273696f6e3d312e302e302e302c2043756c747572653d6e65757472616c2c205075626c69634b6579546f6b656e3d343765373638633534313730633631390501000000274562697845786368616e67652e436f6e646974696f6e732e436f6e646974696f6e7347726f757004000000084f70657261746f72144368696c64436f6e646974696f6e73436f756e74114368696c64436f6e646974696f6e735f30104368696c6447726f757073436f756e74040004002f4562697845786368616e67652e436f6e646974696f6e732e436f6e646974696f6e7347726f75704f70657261746f720200000008214562697845786368616e67652e436f6e646974696f6e732e436f6e646974696f6e02000000080200000005fdffffff2f4562697845786368616e67652e436f6e646974696f6e732e436f6e646974696f6e7347726f75704f70657261746f72010000000776616c75655f5f00080200000001000000010000000904000000000000000c050000005a4562697845786368616e67652e4e616d65644974656d732c2056657273696f6e3d312e302e302e302c2043756c747572653d6e65757472616c2c205075626c69634b6579546f6b656e3d343765373638633534313730633631390504000000214562697845786368616e67652e436f6e646974696f6e732e436f6e646974696f6e03000000084f70657261746f720e4c65667445787072657373696f6e10526967687445787072657373696f6e73040406294562697845786368616e67652e436f6e646974696f6e732e436f6e646974696f6e4f70657261746f7202000000214562697845786368616e67652e4e616d65644974656d732e4e616d65644974656d050000000200000005faffffff294562697845786368616e67652e436f6e646974696f6e732e436f6e646974696f6e4f70657261746f72010000000776616c75655f5f00080200000001000000090700000009080000000507000000214562697845786368616e67652e4e616d65644974656d732e4e616d65644974656d0100000009506174684974656d73042b4562697845786368616e67652e4e616d65644974656d732e4e616d65644974656d506174684974656d5b5d05000000050000000909000000110800000001000000060a0000000454657374070900000000010000000300000004294562697845786368616e67652e4e616d65644974656d732e4e616d65644974656d506174684974656d0500000005f5ffffff294562697845786368616e67652e4e616d65644974656d732e4e616d65644974656d506174684974656d03000000084974656d4e616d650c4e756d65726963496e6465780b537472696e67496e6465780100010805000000060c0000000b5472616e73616374696f6effffffff0a01f3fffffff5ffffff060e0000000a506172616d6574657273ffffffff060f000000045465737401f0fffffff5ffffff06110000000556616c7565ffffffff0a0b

I know for sure this will translate to something meaning full because when I run the following in c# I get a nice string output

row["test"] = string.IsNullOrWhiteSpace(row["TestRow"].ToString()) ? null
: SerializationUtilities.BytesToObject<ConditionsGroup>(((Byte[])row["TestRow"])).ToString();

with the above mentioned code I am getting the following value

(Transaction.Parameters["Test"].Value is equal to Test)

Can I get the same output from SQL so that I need to convert it in c#

What is that value supposed to be? – Tom H Jan 13 '16 at 18:47 — Tom H, Jan 13 '16 at 18:47
How did you come up with that VARBINARY value? – Tom H Jan 13 '16 at 19:06 — Tom H, Jan 13 '16 at 19:06

score 0 · Answer 1 · answered Jan 13 '16 at 19:07

0

Looks like just using CAST works fine for me. My guess is that it has to do with how you came up with the VARBINARY value that you're hard-coding in your script.

DECLARE @test VARBINARY(MAX) = CAST('This is a test' AS VARBINARY(MAX))

SELECT CAST(@test AS VARCHAR(MAX)), @test

answered Jan 13 '16 at 19:07

Tom H

46,766
14
87
128

I have updated the question with new binary values and the corresponding text value. – Gautam Jan 13 '16 at 19:12
@Gautam Try casting to `NVARCHAR(MAX)` maybe? – TT. Jan 13 '16 at 19:18
Using `CAST`, as I've shown above, works fine in SQL. Again, without knowing how you ended up with that particular long string of numbers I can't really give you any more advice. I would look at what is writing that data. Also, use text output for SSMS (-t) and check your results again. I believe that your first `CAST` is actually returning data. It just won't show up in a grid. – Tom H Jan 13 '16 at 19:21

Convert Varbinary to Varchar in select statement

1 Answers1