在SQL Server中从base64字符串转换为varbinary(max) [英] Converting from base64 string to varbinary(max) in SQL Server

问题描述

我在表中以二进制形式存储了PDF文档，存储PDF字节的列的类型为varbinary(max).我想用SQL Studio中的更新文档update记录一条记录，我试图做到这一点的方式如下所示:

I have PDF documents stored in my table as binary, the column that stores the bytes for the PDFs is type varbinary(max). I want to update one record with an updated document in SQL Studio, the way I am attempting to accomplish this is like below

UPDATE table
SET file_bytes=CONVERT(varbinary(max),'JVBERi0xLjYNCiW2JqDQo8PC9UeX...0YNCg==') --this is a base64 string
WHERE id='73c75254-ad86-466e-a881-969e2c6e7a04';

查询运行，但是当我尝试通过网站下载文档时，它抛出一条错误消息，显示为PDF header signature not found.

The query runs, but when I try to download the document (via the website), it throws an error message that reads PDF header signature not found.

这种转换有可能吗?

推荐答案

使用此处描述的方法是可能的:

It is possible by using the approach described here : https://blogs.msdn.microsoft.com/sqltips/2008/06/30/converting-from-base64-to-varbinary-and-vice-versa/

这是一个两步过程，首先声明一个变量:

It's a two-step process, first you declare a variable :

declare @str varchar(max) = '/9j/4AAQSkZJRgABAQEAAAAAAAD/==';

然后，您可以在SQL语句中使用变量，如下所示:

Then you can use the variable in your SQL statement as follow :

INSERT INTO Documents (Name, Body, MIMEType)
VALUES('12446_photo.jpg', cast(N'' as xml).value('xs:base64Binary(sql:variable("@str"))', 'varbinary(max)'), 'image/jpeg');

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