Converting base64 Encoding to String format in SQL

September 15, 2021 Kloudspro SQL Server / DBA Leave a comment

You might have seen at various places, images being represented as normal ASCII characters…those are nothing but the image’s binary data encoded into ASCII format. This encoded format is base64 which is more commonly used these days to encode binary data. This is not necessarily for encoding image’s binary data but can be used to encode other textual data as well. Even though encoded but shouldn’t be used as a way to encrypt sensitive data as this can be decoded back to its original form. Let us see in this post, how we can convert a normal text to base64 format and vice-versa in SQL Server.

In the following example, Kloudspro is a normal string that we convert into base64 format.

DECLARE @string VARCHAR(10) = 'Kloudspro'
SELECT CAST(@string AS VARBINARY(MAX)) FOR XML PATH (''), BINARY BASE64
GO

As we said that base64 is a format to convert binary data into an ASCII format so string is first converted into binary format in above example and then FOR XML clause is being used to convert into base64 format. It gives you output in XML that you can further type cast to VARCHAR to present it as a string –

DECLARE @string VARCHAR(10) = 'Kloudspro'
SELECT * FROM
(
	SELECT CAST(@string AS VARBINARY(MAX)) FOR XML PATH (''), BINARY BASE64 
)a (base64)
GO

You get the following output whereby “Kloudspro” has been converted into base64 format value.

Let us now see how to decode this string back to its original text i.e. base64 to string conversion –

DECLARE @base64txt VARCHAR(20) = 'S2xvdWRzcHJv'
SELECT OriginalText = CAST(CAST(@base64txt AS XML).value('.', 'VARBINARY(MAX)') AS VARCHAR(MAX))
GO

You can not do a direct reverse conversion i.e. from base64 format to binary and then to text…this first needs to be converted into XML and then converted into binary format to finally land into text format. By executing the above script, you get the following output which is nothing but the original text i.e. Kloudspro. You can notice that casing is also preserved between the conversions.