How to decode nvarchar to text (SQL Server 2008 R2)?

Question

I have a SQL Server 2008 R2 table with nvarchar(4000) field nvarchar(4000) .

The data storing this table looks like

'696D616765206D61726B65643A5472'

or

'303131' ("011") .

I see that each char encodes hex.

How can I read this data from a table? I do not want to write a decoding function, I mean that there is an easier way.

PS Sorry for my English.

Answer 1

SQL Server 2008 actually has a built-in encoding and decoding function with hexadecimal encoding!

Example (note the third parameter with a value of "1" when converting your string to VarBinary):

 DECLARE @ProblemString VarChar(4000) = '54657374' SELECT Convert(VarChar, Convert(VarBinary, '0x' + @ProblemString, 1)) 

Link: http://blogs.msdn.com/b/sqltips/archive/2008/07/02/converting-from-hex-string-to-varbinary-and-vice-versa.aspx

The advantage of this approach is that you do not need the "Exec" call, which you usually try to avoid for fear of injections, by the way. The downside is that it only works in SQL Server 2008 and later.

Answer 2

You will need a decoding function, I think the simplest one:

 declare @fld nvarchar(4000) = '696D616765206D61726B65643A5472' exec('SELECT CONVERT(varchar(max),0x' + @fld + ')') --------------- image marked:Tr