BASE64_ENCODE (Transact-SQL)

  • Article

Applies to: Azure SQL Database SQL analytics endpoint and Warehouse in Microsoft Fabric

BASE64_ENCODE converts the value of a varbinary into a base64 encoded varchar.

Transact-SQL syntax conventions

Syntax

BASE64_ENCODE (expression [, url_safe])

Arguments

expression

An expression of type varbinary (n | max)

url_safe

Optional integer literal or expression, which specifies whether the output of the encode operation should be URL-safe. Any number other than 0 evaluates to true. The default value is 0.

Return types

  • Varchar(8000)
  • Varchar(max) if the input is varbinary(max)
  • Varchar(max) if the input is varchar(n) where n > 6000
  • If the input expression is null, the output is null.

Remarks

The encoded string alphabet is that of RFC 4648 Table 1 and may add padding. The URL-safe output uses the base64url alphabet of RFC 4648 Table 2 and doesn't add padding. This function doesn't add any new line characters.

In each case, the database default collation is used. For more information on the supported collations in Microsoft Fabric, see Tables.

If url_safe is true, the base64url string that is generated is incompatible with SQL Server's XML and JSON base64 decoders.

Examples

A. Standard BASE64_ENCODE

In the following example, simple varbinary is base64 encoded.

SELECT Base64_Encode(0xA9) as "Encoded © symbol";

Here is the result set.

------------  
qQ==
(1 row affected)

B. BASE64_ENCODE a string

In the following example, a string is base64 encoded. The string must first be casted to a varbinary.

SELECT BASE64_ENCODE (CAST ('hello world' as varbinary))

Here is the result set.

------------  
aGVsbG8gd29ybGQ=
(1 row affected)

C. BASE64_ENCODE default vs url_safe

In the following example, the first select doesn't specify url_safe, however the second select does specify url_safe.

SELECT BASE64_ENCODE(0xCAFECAFE)

Here is the result set.

------------  
yv7K/g==
(1 row affected)

The following example specifies that the output is URL-safe.

SELECT BASE64_ENCODE(0xCAFECAFE, 1);

Here is the result set.

------------  
yv7K_g
(1 row affected)

Next steps