SHA2

Compute a SHA-2 family digest (SHA-224, SHA-256, SHA-384, or SHA-512) as a lowercase hex string.

Category: hashReturns: STRINGDialect: Standard

Syntax

SHA2(expr, bit_length)

Description

## Overview Computes a SHA-2 family digest of the input string and returns a lowercase hex string representing the hash. The bit_length argument selects the variant: 224, 256, 384, or 512 bits. A bit_length of 0 is treated as 256 for backward compatibility. SHA-2 remains suitable for cryptographic and security-sensitive use cases. For password storage prefer a purpose-built KDF (for example bcrypt, scrypt, or Argon2); for message integrity use HMAC-SHA-256 or larger with a keyed hash. ## Behavior - Returns NULL if either argument is NULL. - Raises an error if bit_length is not one of 0, 224, 256, 384, 512. - Output length in hex characters is bit_length / 4 (for example SHA-256 produces 64 hex characters, SHA-512 produces 128). - Output is lowercase. - Deterministic across calls and engines that implement FIPS 180-4. - Strings are hashed as UTF-8 bytes. ## Algorithm - SHA-2 family per FIPS 180-4. - Output widths: 224, 256, 384, 512 bits. ## Compatibility - Output matches FIPS 180-4 reference vectors across conformant implementations. - Hex case may vary across engines; normalize with LOWER() when cross-comparing.

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.
`bit_length`		Specifies the SHA-2 variant by output bit length. Valid values: 224, 256, 384, 512. A value of 0 is treated as 256.

Examples

-- SHA-256 of a simple string
SELECT SHA2('hello', 256) AS h;

-- SHA-512 produces a longer output
SELECT SHA2('hello', 512) AS h;

-- SHA-224
SELECT SHA2('hello', 224) AS h;

-- 0 is an alias for 256
SELECT SHA2('hello', 0) = SHA2('hello', 256) AS same;  -- true

-- Realistic: build a secure per-row fingerprint for audit
SELECT event_id, SHA2(CONCAT_WS('|', actor, action, CAST(ts AS STRING)), 256) AS audit_fp
FROM security.audit.events
WHERE event_date = DATE '2026-04-19';

-- Realistic: hash an identifier for pseudonymization
SELECT SHA2(user_email, 256) AS pseudo_id
FROM iam.directory.users;

Pitfalls

SHA-2 is a general-purpose hash, not a password-hashing function. For password storage use bcrypt, scrypt, or Argon2. For message authentication use HMAC-SHA-256 or larger.
bit_length must be one of 0 (alias for 256), 224, 256, 384, 512. Other values raise an error.
SHA-224 is truncated from SHA-256 internals with different initialization constants; SHA-224 output is not a prefix of SHA-256 output.
Hex case may differ between engines; normalize with LOWER() when comparing.
SHA-2 is fast. For password hashing, using SHA-2 directly is weaker than a KDF even with a salt.