MASK_EMAIL

Redact an email address for display while preserving the domain.

Category: miscReturns: STRINGDialect: PostgreSql

Syntax

MASK_EMAIL(email)

Description

## Overview Returns a redacted version of the input email address suitable for display in logs, audit trails, and support interfaces. The function preserves the first character of the local part and the entire domain, and replaces the remaining local-part characters with asterisks. Use this function wherever an email must be shown to an operator without exposing the full mailbox. MASK_EMAIL is a display-only redaction, not encryption. The original value cannot be recovered from the masked string, but the masked form still leaks the domain and the first character of the local part. ## Masking pattern - The local part is the substring before the first `@`. The first character is kept verbatim and every remaining character is replaced with `*`. - The `@` separator and the full domain are preserved unchanged. - If the input contains no `@`, the string is returned unchanged. - If the local part is empty, the result is `@domain` with no asterisks. - The total length of the mask matches the local-part length (one visible character plus asterisks). ## Behavior - Returns NULL for NULL input. - Deterministic and side effect free. - Does not validate that the input is a well-formed email address. Combine with IS_EMAIL when validation is required. - Operates on the UTF-8 codepoint level; multi-byte characters count as single characters. ## Compatibility - Matches the PG-compat MASK_EMAIL semantics used for PII redaction in reporting pipelines.

Parameters

Name	Type	Description
`email`		Specifies the email address to mask. Only strings that contain exactly one '@' separator are redacted as an email; other strings are returned unchanged.

Examples

-- Standard mask keeps the first character of the local part and the full domain
SELECT MASK_EMAIL('john.doe@example.com');  -- 'j***@example.com'

-- Short local part still redacts to a single visible character
SELECT MASK_EMAIL('a@example.com');  -- 'a***@example.com'

-- Build a safe display column for support tooling
SELECT customer_id, MASK_EMAIL(email) AS email_display
FROM crm.catalog.customers;

-- Combine with IS_EMAIL to only mask values that look like addresses
SELECT CASE WHEN IS_EMAIL(raw_value) THEN MASK_EMAIL(raw_value) ELSE '***' END AS safe_value
FROM crm.stage.raw_contacts;

-- NULL propagation
SELECT MASK_EMAIL(NULL);  -- NULL

Pitfalls

MASK_EMAIL is not a cryptographic transformation. The domain is preserved verbatim, which can still identify a user (for example, `jane.doe@acme.com` is unique). Combine with tokenization or hashing for stronger privacy guarantees.
Short local parts (1 character) still expose that character. Consider replacing the entire local part with asterisks if even the first character is sensitive.
The function does not validate the input. A string with no `@` is returned unchanged, which could silently leak data if you assumed the column always contained an email. Pair with IS_EMAIL.
The mask length equals the local-part length, which itself may be sensitive (a 10-character local part becomes `j*********`). Use a fixed-length mask if length itself is PII.