How To Update A Varbinary Column With Base64 String To Bytes?

I want to update a varbinary column with base64 string (an image) to bytes in SQL Server. Does anyone know how to do it? I need to have the image in bytes. Thanks!

Solution 1:

You can use the XML Query function xs:base64Binary() to encode and decode Base64 data for you:

• When given varbinary(max) input it returns a varchar(max) result, i.e.: Base64 encoded data.
• When given varchar(max) input it returns a varbinary(max) result. If the input isn't valid Base64 encoded data it returns NULL.

e.g.:

createtable dbo.Demo (
  ID intnotnullidentity(1,1),
  ImageData varbinary(max)
);

insert dbo.Demo (ImageData) values (null);

declare@Base64Datavarchar(max) ='LzlqLzRB';
update dbo.Demo
set ImageData =cast(''as xml).value('xs:base64Binary(sql:variable("@Base64Data"))', 'varbinary(max)') 
where ID =1;

select*from dbo.Demo;

=====

Edit: if you have already stored your base64 data into varbinary(max) you will need to cast it to varchar(max) before supplying it to xs:base64Binary(), e.g.:

createtable dbo.Demo (
  ID intnotnullidentity(1,1),
  ImageData varbinary(max)
);

insert dbo.Demo (ImageData) values ( cast('LzlqLzRB'asvarbinary(max)) );

select*from dbo.Demo; -- base64 characters as varbinaryupdate Dmo
set ImageData =cast(''as xml).value('xs:base64Binary(sql:column("Base64Data"))', 'varbinary(max)') 
from dbo.Demo Dmo
outer apply ( select Base64Data =cast(ImageData asvarchar(max)) ) Cst
where ID =1;

select*from dbo.Demo; -- decoded data as varbinary