35

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

2 Answers 2

48

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)

1
  • 2
    For those who are getting NULL add == at the end of the base64String you are trying to decode Feb 21, 2023 at 16:26
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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.