SQL Server 字符串到 varbinary 的转换 [英] SQL Server string to varbinary conversion

问题描述

好的，问题是需要在 2 个表上完成合并或连接.一个将文件内容存储为 [image] 类型或 varbinary(max)，另一个将文件内容存储为十六进制字符串.如果我将相同的内容上传到两个表中

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

字符串形式的内容(字节数组到字符串)看起来像这样...

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

如果我选择 convert(varbinary(MAX), @contentAsString) 我得到 0x6100700070006C00690063006100740069006F006E

if I do select convert(varbinary(MAX), @contentAsString) I get 0x6100700070006C00690063006100740069006F006E

看起来好像转换是在目标上，但在每个之间放置两个零 (00)，由于缺少更好的词，我将其称为一个字节.

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.

我尝试了论坛上发布的各种更复杂的方法，但都无济于事.任何帮助将不胜感激.

I've tried all sorts of more complicated methods posted across forums but to no avail. Any help would be appreciated.

推荐答案

好的，填充的 00 已得到答复.

Ok, so the padded 00 has been answered.

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...

00 填充是因为 Unicode 或 NVARCHAR 而不是 VARCHAR.

The 00 padding is because of Unicode or NVARCHAR as opposed to VARCHAR.

所以，由于存储的数据在 nvarchar(max) 中，解决方案是这样的:

So, since the stored data is in nvarchar(max), the solution is this:

select CAST(cast(@hexStringNVar as varchar(max)) as varbinary(max)) = 0x6170706C6963...

我确信 convert 也能正常工作，但我的目标 SQL Server 是 2005.

I'm sure that convert would work just as well but my target SQL Server is 2005.

这篇关于SQL Server 字符串到 varbinary 的转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！