Converting from base64 string to varbinary(max) in SQL Server

Question

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';

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.

Is this conversion even possible?

Answer 1

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/==';

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');