TO_BASE64() / FROM_BASE64()

Some data is raw bytes with no characters behind it: an image thumbnail, an encrypted token, the output of a hash function. SQL stores bytes in VARBINARY, a type holding a sequence of byte values from 0 to 255. Raw bytes cannot travel safely through JSON or CSV, and email and HTTP headers have the same problem, because all of them expect printable text and treat some byte values as control codes. Base64 is the standard fix: it rewrites any bytes using 64 printable characters, the letters A to Z and a to z, the digits 0 to 9, plus + and /.

TO_BASE64(bytes) takes VARBINARY and returns the Base64 VARCHAR, and FROM_BASE64(text) reverses it. Text you want encoded has to become bytes first, which is the job of TO_UTF8, and decoded bytes become readable text again through FROM_UTF8. Take that pair on trust for now; the last section of this lesson explains what it does.

How 3 bytes become 4 characters

Each Base64 character stands for 6 bits, a value from 0 to 63 that picks a character from the alphabet. The encoder reads its input in groups of 3 bytes, which is 24 bits, and writes 4 characters for each group. Take 'Hi': its bytes are 72 and 105, 01001000 01101001 in bits, 16 bits in all, split into groups of 6:

The group came up 8 bits short, so the encoder fills the third character with zero bits and writes = in place of the fourth. A 1-byte input ends in ==, a 2-byte input in =, and a 3-byte input needs no padding:

SELECT
  TO_BASE64(TO_UTF8('H')) AS one_byte,
  TO_BASE64(TO_UTF8('Hi')) AS two_bytes,
  TO_BASE64(TO_UTF8('Hi!')) AS three_bytes,
  FROM_UTF8(FROM_BASE64('SGkh')) AS decoded

• +33%

Decoding a field someone else wrote

APIs often return a page cursor that looks like noise and turns out to be Base64 of plain parameters. Decoding the cursors in a request log tells you which page each call asked for, and since the decoded text is a key-value string, SPLIT_TO_MAP reads the page number out.

SELECT
  request_id,
  FROM_UTF8(FROM_BASE64(next_cursor)) AS decoded,
  CAST(SPLIT_TO_MAP(FROM_UTF8(FROM_BASE64(next_cursor)), '&', '=')['page'] AS INTEGER) AS page
FROM api_requests

Where Base64 turns up in data you query

• Binary fields in JSON exports, since JSON has no byte type
• The 3 dot-separated parts of a JSON Web Token, in the URL-safe alphabet
• HTTP Basic auth headers: the word Basic, a space, then Base64 of user:password
• Data URIs that embed small images inside web pages and stylesheets

The URL-safe alphabet

The characters + and / already mean something inside URLs and file paths, so the URL-safe variant of Base64 writes - and _ in their places and keeps the other 62 characters. TO_BASE64URL and FROM_BASE64URL use that alphabet. The example builds the URL-safe form with 2 REPLACE calls so each swap is visible, and TO_BASE64URL(TO_UTF8('???>>>')) returns the same string in a single call. Text containing - or _ came from the URL-safe encoder, and handing it to the standard decoder raises an error.

SELECT
  TO_BASE64(TO_UTF8('???>>>')) AS standard,
  REPLACE(REPLACE(TO_BASE64(TO_UTF8('???>>>')), '+', '-'), '/', '_') AS url_safe

A hash function returns a 32-byte digest. How many characters does TO_BASE64 write for it?

• It writes 32, one Base64 character per byte
• It writes 43, since 256 bits over 6 bits rounds up to 43
• It writes 64, 2 characters for every byte
• It writes 44: 10 full groups give 40, and 2 leftover bytes give 4

Decoding reads inside out. In FROM_UTF8(FROM_BASE64('SGkh')), the inner FROM_BASE64 turns the 4 characters into 3 bytes, and FROM_UTF8 turns those bytes into the text 'Hi!'. Encoding runs the same 2 steps in reverse: TO_UTF8 makes the bytes, and TO_BASE64 wraps them as text. Nest a pair the other way round and the inner call receives VARCHAR where it expects VARBINARY, so the query fails to resolve.

About this lesson

This section is part of the String Functions: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.