如何将nvarchar解码为文本（SQL Server 2008 R2）？ [英] How to decode nvarchar to text (SQL Server 2008 R2)?

问题描述

我有一个包含 nvarchar（4000）字段的SQL Server 2008 R2表。

存储此表格的数据如

'696D616765206D61726B65643A5472'

或

'303131'（011）。

我看到每个字符的编码为十六进制。

如何从表中读取这些数据？我不想要写解码功能，我的意思是存在更简单的方法。

解决方案

SQL Server 2008实际上具有内置的十六进制编码和解码功能！

示例（注意将字符串转换为VarBinary时，值为1的第三个参数）：

  DECLARE @ProblemString VarChar（4000）='54657374'
 SELECT Convert（VarChar，Convert（VarBinary，'0x'+ @ProblemString，1））
  / pre> 
 
 
参考： http://blogs.msdn.com/b/sqltips/archive/2008/07/02/converting-from-hex- string-to-varbinary-and-vice-versa.aspx  
 
 
 
这种方法的优点是您不需要Exec调用，你通常尽量避免，因为害怕注射等等。缺点是它只适用于SQL Server 2008和更高版本。
 
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.
解决方案
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.

                        
这篇关于如何将nvarchar解码为文本（SQL Server 2008 R2）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！