Lenni's Technology Blog

Base64 encoding is a method of converting binary data into an ASCII string format by translating it into a radix-64 representation. Base64 decoding is the reverse process, converting an ASCII string back into binary data. Encoding is often used to safely transmit binary data over text-based protocols, such as HTTP or email, though note that encoding binary data increases its size by approximately 33%.

This blog post demonstrates how to use the new BASE64_ENCODE and BASE64_DECODE functions in T-SQL. The examples include both standard Base64 encoding (which may include characters like + and / that are not URL-safe) and URL-safe Base64 encoding (which replaces + with - and / with _).

Encoding with BASE64_ENCODE and Decoding with BASE64_DECODE

First let’s perform standard Base64 encoding and decoding. Run this code snippet to encode a binary value into a string using BASE64_ENCODE:

DECLARE @Binary varbinary(max) = 0xCAFECAFE
SELECT StandardEncoded = BASE64_ENCODE(@Binary)

Result:

yv7K/g==

Now use BASE64_DECODE to convert the encoded string back to its original binary form:

DECLARE @StandardEncoded varchar(max) = 'yv7K/g=='
SELECT Binary = BASE64_DECODE(@StandardEncoded)

Result:

0xCAFECAFE

Notice how the encoded string for the same binary content contains characters like / and +, which are not safe for use in URLs.

URL-Safe Base64 Encoding

To create a URL-safe version, use the second parameter of BASE64_ENCODE (where 1 means to perform URL-safe encoding):

DECLARE @Binary varbinary(max) = 0xCAFECAFE
SELECT UrlSafeEncoded = BASE64_ENCODE(@Binary, 1)

Result:

yv7K_g

Notice that the URL-safe encoded string replaces / with _ (it would also replace + with -). And now, use BASE64_DECODE to convert the URL-safe encoded string back to its original binary form:

DECLARE @UrlSafeDecoded varchar(max) = 'yv7K_g'
SELECT Binary = BASE64_DECODE(@UrlSafeDecoded)

Result:

0xCAFECAFE

You can see that both the standard and URL-safe encoded strings decode back to the same original binary data.

So then, why not always use URL-safe Base64 encoding? Although BASE64_DECODE can handle both standard and URL-safe Base64 formats in SQL Server, standard Base64 remains the default because it aligns with established standards (RFC 4648) and ensures compatibility with most systems, libraries, and protocols that expect the traditional +, /, and = characters. The URL-safe variant should be used only when the encoded data will appear in contexts like URLs, filenames, or cookies that restrict those characters. In short, use standard Base64 for interoperability and URL-safe Base64 only when required by the target environment.

Practical Examples

Here is a more practical example of using Base64 encoding and decoding in T-SQL. In this case, we will construct a JSON object that includes an embedded image. With the binary image content embedded as a Base64-encoded string, the JSON object can be easily transmitted or stored in text-based formats. In this case, the JSON object could be used in web applications, REST APIs, or other scenarios where images need to be included in JSON responses. Note that the same approach can be used for embedding images in HTML or XML documents, all using T-SQL.

DECLARE @ProductId int = 6
DECLARE @ProductImage varbinary(max) = 0xCAFECAFE89504E470D0A1A0A0000000D49484452
SELECT
  ProductJson = JSON_OBJECT(
    'productId' : @ProductId,
    'thumbnail' : 'data:image/png;base64,' || BASE64_ENCODE(@ProductImage)
  )

Result:

{"productId":6,"thumbnail":"data:image/png;base64,yv7K/olQTkcNChoKAAAADUlIRFI="}

Here is another example, where we construct a binary token for use in a URL. The token is first created as a binary value, then encoded using URL-safe Base64 encoding, and finally embedded into a URL string. This approach is useful for securely transmitting tokens in web applications via a URL, such as for authentication or authorization purposes.

DECLARE @Token varbinary(max) = CAST('user1|expiry=2025-12-31' AS varbinary)
SELECT @Token
DECLARE @UrlSafeEncodedToken varchar(max) = BASE64_ENCODE(@Token, 1)
SELECT @UrlSafeEncodedToken
DECLARE @Url varchar(max) = 'https://api.myapp.com/download?token=' || @UrlSafeEncodedToken
SELECT @Url

First, the string token is represented as binary data:

0x75736572317C6578706972793D323032352D31322D3331

Then it is encoded using URL-safe Base64:

dXNlcjF8ZXhwaXJ5PTIwMjUtMTItMzE

Finally, that encoded token is placed directly in the URL:

https://api.myapp.com/download?token=dXNlcjF8ZXhwaXJ5PTIwMjUtMTItMzE

Observe that the resulting URL contains a token that is safe for inclusion in URLs, thanks to the URL-safe Base64 encoding. Also notice the use of the ANSI SQL standard concatenation operator (||) to build the URL string, as you learned about in the previous lab.

Summary

The new BASE64_ENCODE and BASE64_DECODE functions are small additions that solve a very practical problem. They make it easy to convert binary values into portable text and back again, directly in T-SQL.

Use standard Base64 when you need conventional encoded output, and use URL-safe Base64 when the encoded value will appear in a URL, token, route segment, or query string. Just remember that Base64 is not encryption, and that encoding increases the size of the data.