How Do I Convert a BASE64 String Into Its Native Format?

Salesforce stores the Attachment and ContentVersion bodies as BASE64 strings — a common question is “How can I turn a database Attachment into a file my application can read?”. Since the data is stored as a BASE64 string, this is equivalent to “How do I convert a BASE64 string into its binary equivalent?”.

The answer to this question is database dependent.

Oracle

To perform BASE64 to binary conversion in Oracle:

• Download the base64decode() function from the link on oracle-base.com.
• Use SQL to convert the body of Attachments to their binary format:

  • SELECT id, BASE64DECODE(body) AS rawbody FROM Attachment

You may want to use the BASE64DECODE() function to create a permanent view to the Attachment or ContentVersion table.

SQL Server

To convert a BASE64 column to a text column in SQL/Server:

SELECT
    CONVERT(
        VARCHAR(MAX),
        CAST(” AS XML).value(‘xs:base64Binary(sql:column(“Body”))’, ‘VARBINARY(MAX)’)
    ) AS RESULT
FROM
    Attachment

To convert a BASE64 column to a binary column in SQL/Server:

SELECT
    CAST(” AS XML).value(‘xs:base64Binary(sql:column(“Body”))’, ‘VARBINARY(MAX)’)
FROM
    Attachment