Decode Base64 String Natively in SQL Server

Question

I have a varchar column in a table in SQL Server that holds a Base64-encoded text string,
which I would like to decode into its plain text equivalent.

Does SQL Server have any native functionality to handle this type of thing?

Here is a sample base64 string:

cm9sZToxIHByb2R1Y2VyOjEyIHRpbWVzdGFtcDoxNDY4NjQwMjIyNTcxMDAwIGxhdGxuZ3tsYXRpdHVkZV9lNzo0MTY5ODkzOTQgbG9uZ2l0dWRlX2U3Oi03Mzg5NjYyMTB9IHJhZGl1czoxOTc2NA==

which decodes to:

role:1 producer:12 timestamp:1468640222571000 latlng{latitude_e7:416989394 longitude_e7:-738966210} radius:19764

Answer 1

Figured it out:

SELECT 
    CONVERT
    (
        VARCHAR(MAX), 
        CAST('' AS XML).value('xs:base64Binary(sql:column("BASE64_COLUMN"))', 'VARBINARY(MAX)')
    ) AS RESULT
FROM
    (
        SELECT 'cm9sZToxIHByb2R1Y2VyOjEyIHRpbWVzdGFtcDoxNDY4NjQwMjIyNTcxMDAwIGxhdGxuZ3tsYXRpdHVkZV9lNzo0MTY5ODkzOTQgbG9uZ2l0dWRlX2U3Oi03Mzg5NjYyMTB9IHJhZGl1czoxOTc2NA==' AS BASE64_COLUMN
    ) A

Output:

role:1 producer:12 timestamp:1468640222571000 latlng{latitude_e7:416989394 longitude_e7:-738966210} radius:19764

Just swap out BASE64_COL_NAME for your column name, or you can replace sql:column("BASE64_COLUMN") with sql:variable("@base64variable") if you want to use a declared variable e.g. if you are making a function or something.

It makes use of an XSL transform using built-in XML functionality (since SQL Server 2005)

Answer 2

Just as an alternative, Azure SQL Database supports decoding base64 encoded values into varbinary which can then be converted into a varchar value.

convert(varchar(max), BASE64_DECODE([BASE64_ENCODED_COLUMN_NAME]))

BASE64_DECODE (Transact-SQL)