MD5

Compute the MD5 digest of a string as a 32-character lowercase hex string.

Syntax

MD5(expr)

Description

## Overview Computes the MD5 message digest of the input string and returns a 32-character lowercase hexadecimal text representation of the 128-bit hash. MD5 is deterministic and fast, making it a common choice for row-level fingerprints, bucketing, and change detection in ETL pipelines. MD5 is not considered cryptographically secure. Both collision and length-extension attacks are known. Do not use MD5 for signatures, password storage, or any context where an adversary can craft inputs; use SHA2 (256 or larger) for those workloads. ## Behavior - Returns NULL for NULL input. - Output is a 32-character hex string, lowercase. - Deterministic and stable across calls and engines that implement RFC 1321. - Empty string input returns 'd41d8cd98f00b204e9800998ecf8427e'. - Strings are hashed as UTF-8 bytes. ## Algorithm - MD5 per RFC 1321. - Output width: 128 bits (16 bytes), rendered as 32 hex characters. ## Compatibility - Output matches the RFC 1321 reference vectors across all conformant implementations. - Case of the hex output varies by engine; when comparing across systems normalize with LOWER().

Parameters

Name	Type	Description
expr		Specifies the string value to hash. The string is hashed as its UTF-8 byte sequence. Returns NULL for NULL input.

Examples

-- MD5 of a simple string
SELECT MD5('hello') AS h;  -- '5d41402abc4b2a76b9719d911017c592'

-- MD5 of the empty string
SELECT MD5('') AS h;  -- 'd41d8cd98f00b204e9800998ecf8427e'

-- Row fingerprint via concatenation
SELECT MD5(CONCAT_WS('|', col1, col2, col3)) AS row_hash
FROM ingestion.staging.daily_rows;

-- Output length is always 32 hex characters
SELECT LENGTH(MD5('test')) AS len;  -- 32

-- Realistic: detect changed rows between snapshots
SELECT a.user_id
FROM analytics.snapshots.users_latest a
JOIN analytics.snapshots.users_prev b ON a.user_id = b.user_id
WHERE MD5(CONCAT_WS('|', a.display_name, a.email)) <> MD5(CONCAT_WS('|', b.display_name, b.email));

Pitfalls

• MD5 is broken for security purposes. Do not use for signing, authentication, or password hashing. Use SHA2 with 256 or more bits for those cases.
• Collisions are constructively feasible; two different documents can share the same MD5. Fingerprints are still useful for change detection but are not a proof of identity.
• Some engines produce uppercase output. Normalize with LOWER() when comparing hashes across systems.
• MD5 of a composite key built from concatenated columns is sensitive to the separator choice. Use CONCAT_WS with a character that cannot appear in the data to avoid collisions from 'ab|c' vs 'a|bc'.
• String inputs are hashed as UTF-8 bytes; normalizing Unicode or changing column collation silently changes the hash.

See Also

• sha
• sha1
• sha2
• hash
• crc32