SQL Server 字符串到 varbinary 的转换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14145904/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server string to varbinary conversion
提问by Matthew Weir
Ok, the problem is that there's a merger or join that needs to be done on 2 tables. One has file content stored as an [image] type or varbinary(max), the other has the file content stored as a hex string. if I upload the same content into both tables
好的，问题是需要在 2 个表上完成合并或连接。一个将文件内容存储为 [image] 类型或 varbinary(max)，另一个将文件内容存储为十六进制字符串。如果我将相同的内容上传到两个表中
the content as string (bytearray to string) would look like like this...
作为字符串（字节数组到字符串）的内容看起来像这样......
'application/vnd.xfdl;content-encoding="base64-gzip"
H4sIAAAAAAAAC+y9e1fjONI4/H9/Cg173idwFgIJl+5m6MzPJAayE+KsnXQPs8+cHJMY8HZi57ET
aObMh3918UW2Jcdyrmbg7E7HtqpUpSqVSqWSdPHLj/EIPBuOa9rWl51K+WgHGNbAHprW45edpqYc
fPp0+vmgsvNL7cPFb1eNFoDlLffLztN0Ojk/PHx5eSl3Zo4hDx+N8sAeH6Iyh2fl0x1S8Hwwc6f2'
...
the content as image looks like (and this is ultimately what I want it to look like)
作为图像的内容看起来像（这最终是我想要的样子）
0x6170706C69636174696F6E
if I do select convert(varbinary(MAX), @contentAsString)I get 0x6100700070006C00690063006100740069006F006E
如果我选择convert(varbinary(MAX), @contentAsString)我得到0x6100700070006C00690063006100740069006F006E
it appears as though the conversion is on target but putting two zeros (00) between each, I'll call it a byte for lack of better words.
看起来好像转换是在目标上，但在每个之间放置两个零 (00)，由于缺少更好的词，我将其称为一个字节。
I've tried all sorts of more complicated methods posted across forums but to no avail. Any help would be appreciated.
我尝试了各种论坛上发布的更复杂的方法，但都无济于事。任何帮助，将不胜感激。
回答by Woot4Moo
From MSDN
来自MSDN
In SQL Server 2008, these conversions are even more easier since we added support directly in the CONVERT built-in function. The code samples below show how to perform the conversion(s):
declare @hexstring varchar(max); set @hexstring = '0xabcedf012439'; select CONVERT(varbinary(max), @hexstring, 1); set @hexstring = 'abcedf012439'; select CONVERT(varbinary(max), @hexstring, 2); go declare @hexbin varbinary(max); set @hexbin = 0xabcedf012439; select CONVERT(varchar(max), @hexbin, 1), CONVERT(varchar(max), @hexbin, 2); go
在 SQL Server 2008 中，这些转换更加容易，因为我们直接在 CONVERT 内置函数中添加了支持。下面的代码示例显示了如何执行转换：
declare @hexstring varchar(max); set @hexstring = '0xabcedf012439'; select CONVERT(varbinary(max), @hexstring, 1); set @hexstring = 'abcedf012439'; select CONVERT(varbinary(max), @hexstring, 2); go declare @hexbin varbinary(max); set @hexbin = 0xabcedf012439; select CONVERT(varchar(max), @hexbin, 1), CONVERT(varchar(max), @hexbin, 2); go
回答by Matthew Weir
Ok, so the padded 00 has been answered.
好的，所以填充的 00 已被回答。
DECLARE @hexStringNVar nvarchar(max)
DECLARE @hexStringVAR varchar(max)
SET @hexStringNVar = '{my hex string as described above}'
SET @hexStringVAR = '{my hex string as described above}'
select CONVERT(varbinary(MAX), @hexStringNVar)) = 0x6100700070006C00690063...
select CONVERT(varbinary(MAX), @hexStringVAR)) = 0x6170706C6963...
The 00padding is because of Unicode or NVARCHARas opposed to VARCHAR.
的00填充是因为Unicode或NVARCHAR而不是VARCHAR。
So, since the stored data is in nvarchar(max), the solution is this:
因此，由于存储的数据在 中nvarchar(max)，解决方案是这样的：
select CAST(cast(@hexStringNVar as varchar(max)) as varbinary(max)) = 0x6170706C6963...
I'm sure that convertwould work just as well but my target SQL Server is 2005.
我确信这convert也能正常工作，但我的目标 SQL Server 是 2005。
