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

Answer 2

To select a bare Base64 value in SQL Server, without any XML node around it, you just need an unnamed column in FOR XML

SELECT CAST(fancyImage AS varbinary(max))
FROM #demo
FOR XML PATH(''), BINARY BASE64;

Or as a correlated subquery

SELECT
  myBase64 = (
    SELECT CAST(fancyImage AS varbinary(max))
    FOR XML PATH(''), BINARY BASE64
  )
FROM #demo;

db<>fiddle

Answer 3

I think it was v2008 of SQL-Server, when base64 was made the default in XML for binaries (before it was a hex string). No need to specify this explicitly.

(The option BINARY BASE64 is needed with mode AUTO...)

Just to demonstrate the back and forth I declare some text (a chain of characters) and cast it to binary (the same chain of bytes, but not a string any more):

DECLARE @someText    VARCHAR(100)    = 'This is just some text...';
DECLARE @binary      VARBINARY(MAX)  = CAST(@someText AS VARBINARY(MAX));

--In this case it's okay to rely on implicit casting: easy approach

DECLARE @base64_easy VARCHAR(100) = (SELECT @binary FOR XML PATH('')); 

--Just to demonstrate that the base64 we found (VGhpcyBpcyBqdXN0IHNvbWUgdGV4dC4uLg==) is correct we reconvert it simply by casting it to XML and using .value() to retrieve it as binary:

DECLARE @reConverted VARBINARY(MAX) = (SELECT CAST(@base64_easy AS XML).value('.','varbinary(max)'));

--Casting this chain of bytes into a varchar again will show its (unchanged) content:

SELECT CAST(@reConverted AS VARCHAR(100));

All of this can be used within ad-hoc queries.

Hint:

The more explicit statement SELECTs the value into XML and reads this into text via .value()
(The ,type is needed to allow for XML methods)

DECLARE @base64 VARCHAR(100) = (SELECT @binary FOR XML PATH(''), type).value('.','nvarchar(max)'); --VGhpcyBpcyBqdXN0IHNvbWUgdGV4dC4uLg==