7

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

Data that stores this table look like

'696D616765206D61726B65643A5472'

or

'303131' ("011").

I see that each char is encoding to hex.

How can I read those data from table? I don't want write decoding function, I mean that simpler way exists.

P.S. Sorry for my English.

Pankaj
  • 9,749
  • 32
  • 139
  • 283
xieergai
  • 165
  • 2
  • 6

2 Answers2

4

SQL Server 2008 actually has a built-in hex-encoding and decoding feature!

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

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

Ref: 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 don't need the "Exec" call, which you generally try to avoid, for fear of injection among other things. The disadvantage is that it only works in SQL Server 2008 and later.

Tao
  • 13,457
  • 7
  • 65
  • 76
2

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

declare @fld nvarchar(4000) = '696D616765206D61726B65643A5472'

exec('SELECT CONVERT(varchar(max),0x' + @fld + ')')

---------------
image marked:Tr
Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • declare @fld nvarchar(4000) set @fld = '696D616765206D61726B65643A5472' exec('SELECT CONVERT(varchar(max),0x' + @fld + ')') I think this will be more precise – Pankaj Jun 07 '11 at 10:20
  • True, although for what its worth `declare @v type = value` is supported in sql 2k8 – Alex K. Jun 07 '11 at 10:33