TO_HEX() / FROM_HEX()

Hex writes each byte as 2 characters from 0 to 9 and A to F, each character standing for 4 bits. It takes twice the space of the raw bytes, which is more than Base64 takes. In return every byte stays visible as its own pair, something Base64 cannot give. That's why hash digests and checksums are written in hex, the v1 value in a signature header among them, and why engineers reach for it when they need to see what bytes a column really holds.

TO_HEX(bytes) returns a VARCHAR of hex digits, always uppercase. FROM_HEX(text) accepts upper or lower case and returns the VARBINARY those digits describe. It raises an error for an odd number of digits or any character outside 0-9 and A-F.

SELECT
  TO_HEX(TO_UTF8('Hello')) AS hello_hex,
  TO_HEX(TO_UTF8('é')) AS e_acute_hex,
  FROM_UTF8(FROM_HEX('48656c6c6f')) AS from_lowercase

Read hello_hex 2 digits at a time. The first digit of a pair counts 16s, the second counts 1s, and the letters A to F stand for 10 to 15, so 6C is 6 times 16 plus 12, which is 108, the byte for 'l'.

é came back as 2 pairs, C3A9, because UTF-8 spends 2 bytes on it; the next section explains why.

Comparing digests when the case differs

A hash function such as SHA256(bytes) returns its digest as 32 bytes of VARBINARY. Raw bytes cannot sit safely in a header or a JSON field, as the Base64 section showed, and a text column has the same limit, so senders write those 32 bytes out as 64 hex characters, and that text is what you end up comparing against. Many senders write it in lowercase, while TO_HEX writes uppercase, and a plain string comparison fails on every row even when the bytes agree. In the switcher, computed_hex is your side's TO_HEX output for each event's SHA-256 digest, sent_digest is the partner's lowercase copy, and each tab checks them a different way.

String =

Comparing the uppercase TO_HEX output with the lowercase text returns false for both rows, although the 2 digests describe identical bytes.

SELECT
  event_id,
  LENGTH(sent_digest) AS hex_chars,
  computed_hex = sent_digest AS digest_matches
FROM signatures

LOWER

Lowercasing the computed side puts both strings in the same case, and the comparison holds.

SELECT
  event_id,
  LENGTH(sent_digest) AS hex_chars,
  LOWER(computed_hex) = sent_digest AS digest_matches
FROM signatures

Bytes =

FROM_HEX turns both texts back into bytes and compares those, so case stops mattering. Against a live hash, the right side is the SHA256 output itself.

SELECT
  event_id,
  LENGTH(sent_digest) AS hex_chars,
  FROM_HEX(sent_digest) = FROM_HEX(computed_hex) AS digest_matches
FROM signatures

Base64

• Spends 4 characters per 3 bytes
• A 32-byte digest takes 44 characters
• Case-sensitive: 'a' and 'A' are different values
• Byte boundaries hidden inside 6-bit groups
• Found in tokens, JSON binary fields, attachments

Hex

• Spends 2 characters per byte
• A 32-byte digest takes 64 characters
• Either case on input; TO_HEX writes uppercase
• Each byte readable as its own pair
• Found in hashes, checksums, byte-level debugging

Hex that stands for a number

Some hex text encodes a number: a color such as #1E90FF, a hex ID, a memory address in an error log. FROM_HEX returns bytes, which is the wrong shape there. FROM_BASE(text, 16) reads the digits as a BIGINT, and TO_BASE(number, 16) writes a number back as lowercase hex. A color gives each channel 2 hex digits, red first, then green, then blue, so SUBSTR, the short spelling of SUBSTRING, slices each channel out first.

SELECT
  brand,
  hex_color,
  FROM_BASE(SUBSTR(hex_color, 2, 2), 16) AS red,
  FROM_BASE(SUBSTR(hex_color, 4, 2), 16) AS green,
  FROM_BASE(SUBSTR(hex_color, 6, 2), 16) AS blue,
  TO_BASE(FROM_BASE(SUBSTR(hex_color, 2, 2), 16), 16) AS red_back
FROM brand_colors

A partner sends sent_digest as lowercase hex, and computed holds the matching SHA256 bytes as VARBINARY. Which condition is TRUE for a matching row?

• TO_HEX(computed) = sent_digest, since both sides are now hex text
• FROM_HEX(sent_digest) = computed, since both sides are now bytes
• UPPER(sent_digest) = computed, since the case now matches TO_HEX
• FROM_BASE(sent_digest, 16) = computed, since both sides are numbers

When hex stands for bytes that encode text, getting the text back takes 2 nested calls: FROM_HEX turns the digits into VARBINARY, and FROM_UTF8 reads those bytes as characters. FROM_UTF8(FROM_HEX('436166C3A9')) returns 'Café', 5 bytes and 4 characters, with C3A9 as the é. FROM_BASE on the same 10 digits would return one large BIGINT, a number with no characters in it.

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.