Decode a hexadecimal string into binary data.
UNHEX(str)## Overview Decodes a hexadecimal string into binary data (BYTEA). Each pair of hex characters is interpreted as a single byte. Accepts mixed case (0-9, A-F, a-f) and requires an even number of characters. Use UNHEX when a system has serialized binary as hex text (common for hashes, signatures, short byte arrays) and you need the raw bytes for comparison, hashing, or re-encoding. ## Behavior - Returns NULL for NULL input. - Accepts both uppercase and lowercase hex digits. - Requires an even number of characters; odd-length input raises an error (or returns NULL in some engines). - Non-hex characters raise an error. - Does not accept a '0x' prefix; strip before calling. - Empty input returns empty binary. - Output is BYTEA. ## Algorithm - RFC 4648 Section 8 (Base16) decoding, case-insensitive input. ## Compatibility - Most SQL dialects expose UNHEX with equivalent semantics. Behavior on malformed input may differ: some raise, others return NULL. Treat malformed input as an error and pre-validate when mixing engines.
| Name | Type | Description |
|---|---|---|
str | Specifies the hexadecimal string to decode. Must contain only valid hex characters (0-9, A-F, a-f) and must have an even character count. Returns NULL for NULL input. |
-- Decode to binary
SELECT UNHEX('48656C6C6F') AS bytes;-- Cast binary to text
SELECT CAST(UNHEX('48656C6C6F') AS STRING) AS text; -- 'Hello'-- Round-trip with HEX
SELECT HEX(UNHEX('DEADBEEF')) AS back;-- Empty input yields empty binary
SELECT UNHEX('') AS bytes;-- Realistic: decode stored hex signatures
SELECT event_id, UNHEX(signature_hex) AS signature_bytes
FROM security.audit.signed_events
WHERE created_date = DATE '2026-04-19';