How to convert varbinary(max) to base64 SQL Server 2014

Question

I have an Image saved as varbinary(max) in SQL Server 2014:

0xFFD8FFE115064578696600004D4D002A0000000800070...........

I want to convert it to Base64 To use it in Flutter. I tried

SELECT CAST('' as varbinary(max)) FOR XML PATH(''), BINARY BASE64

and get :

MHhGRkQ4RkZFMTE1MDY0NTc4Njk2NjAwMDA0RDREMDAyQTAwMDAwMDA4MDAwN..........

But according to this site I should get:

/9j/4RUGRXhpZgAATU0AKgAAAAgABwESAAMAAAABAAEAAAEaAAUAAAABAAAAYgEbAA........

So how to convert varbinary(max) to base64?

Answer 1

Why are you attempting to CAST() the varbinary data? You just need to select it as an element or an attribute for the varbinary value to get base64 encoded...

/*
 * Data setup...
 */
if object_id('tempdb..#demo') is not null
  drop table #demo;
create table #demo (
  fancyImage varbinary(max)
);
insert #demo (fancyImage) values (0xFFD8FFE115064578696600004D4D002A000000080007);

/*
 * Select as an element containing base64 data
 */
select fancyImage as [base64DemoElement]
from #demo
for xml path(''), binary base64;

/*
 * Select as an attribute containing base64 data
 */
select fancyImage as [@base64Attribute]
from #demo
for xml path('demoElement'), binary base64;

The first select outputs the base data in an element:

<base64DemoElement>/9j/4RUGRXhpZgAATU0AKgAAAAgABw==</base64DemoElement>

The second select outputs the base64 data in an attribute:

<demoElement base64Attribute="/9j/4RUGRXhpZgAATU0AKgAAAAgABw==" />

Following comments discussion with @DaleK, a third alternative to return the bare base64 characters without any XML tags:

select (
  select top 1 cast(fancyImage as varbinary(max)) as [base64DemoElement]
  from #demo
  for xml path(''), type, binary base64
  ).value('.', 'varchar(max)') as [Base64 characters];

Which outputs:

Base64 characters
/9j/4RUGRXhpZgAATU0AKgAAAAgABw==