Compute the SHA-1 digest of a string as a 40-character lowercase hex string.
SHA1(expr)## Overview Computes the SHA-1 message digest of the input string and returns a 40-character lowercase hex string representing the 160-bit hash. Functionally identical to SHA. SHA-1 is deprecated for cryptographic use because practical collision attacks exist. It remains acceptable for deterministic fingerprints, deduplication, and lineage identifiers where only accidental collisions matter; switch to SHA2 for any adversarial context. ## Behavior - Returns NULL for NULL input. - Output is a 40-character hex string, lowercase. - Deterministic across calls and engines that implement RFC 3174. - Strings are hashed as UTF-8 bytes. - Output is identical to SHA for the same input. ## Algorithm - SHA-1 per RFC 3174. - Output width: 160 bits (20 bytes), rendered as 40 hex characters. ## Compatibility - Output matches RFC 3174 / FIPS 180-4 reference vectors across conformant implementations. - Case of hex output may vary across engines; normalize with LOWER() when comparing.
| 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. |
-- SHA-1 of a simple string
SELECT SHA1('hello') AS h; -- 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d'-- SHA-1 of the empty string
SELECT SHA1('') AS h;-- Length is always 40 hex characters
SELECT LENGTH(SHA1('test')) AS len; -- 40-- Row fingerprint
SELECT user_id, SHA1(CONCAT_WS('|', first_name, last_name, email)) AS fp
FROM iam.directory.users;-- Realistic: detect changed rows between snapshots
SELECT a.user_id
FROM analytics.snapshots.users_latest a
JOIN analytics.snapshots.users_prev b USING (user_id)
WHERE SHA1(a.display_name) <> SHA1(b.display_name);