SQL: BASE64 ENCODE/DECODE

Transact-SQL does not include any built in function to convert binary type into base64 string and vice versa. However, we can use XML built-in options in order to convert VARBINARY into Base64.

FOR XML and binary data

When you use the FOR XML clause in a SELECT statement, regardless of the mode used, any BINARY, LONG BINARY, IMAGE, or VARBINARY columns are output as attributes that are automatically represented in base64-encoded format.

Note, the queries below have been optimised for use in Salesforce Marketing Cloud.

ENCODE

Cross Apply enables to execute SQL XML query on all rows of the database table instead of running SQL Select query on single XML data.

SELECT
  b.decodedValue,
  a.[col] AS encodedValue
FROM
  [de_name_here] AS b
CROSS APPLY
  (
    SELECT
     CONVERT(varbinary(max), CONVERT(varchar(max),[decodedValue]))
     FOR XML PATH ('')
  ) AS a([col])

Double check the output using a tool like: https://www.base64decode.org

If the output doesn’t seem quite right, I would suggest honing in on the varbinary conversion. To compensate for any abnormalities, I recommend taking your string value from the DE, converting it to varchar and then converting that to varbinary.

CONVERT(varbinary(max), CONVERT(varchar(max),stringToEncodeHere))

DECODE

Adding a varbinary value into an XML type stores it as base64 encoded, and extracting the value the systems already knows how to decode it:

SELECT
  encodedValue,
  CONVERT(varchar(MAX), CONVERT(XML,encodedValue).value('.', 'varbinary(max)') ) AS decode
FROM
  [de_name_here]

What is Base64?

Base64 is an encoding algorithm that allows you to transform any characters into an alphabet which consists of Latin letters, digits, plus, and slash. Thanks to it, you can convert Chinese characters, emoji, and even images into a “readable” string, which can be saved or transferred anywhere.

— https://base64.guru/learn/what-is-base64

If you need a refresher on BASE64 checkout the video below