MASK_SSN

Redact a Social Security Number for display while preserving the last four digits.

Category: miscReturns: STRINGDialect: PostgreSql

Syntax

MASK_SSN(ssn)

Description

## Overview Returns a redacted version of the input Social Security Number suitable for display in logs, audit trails, and operator UIs. Every digit except the last four is replaced with an asterisk; hyphens in the standard XXX-XX-XXXX format are preserved in place. Use this function wherever SSN data must be shown without exposing the full number. MASK_SSN is a display-only redaction. ## Masking pattern - Digits are replaced with `*` except for the last four, which are preserved verbatim. - Non-digit characters (hyphens, spaces) are preserved in place. - If the input has fewer than four digits, all digits are redacted. - The total string length matches the input length exactly. ## Behavior - Returns NULL for NULL input. - Deterministic and side effect free. - Does not validate the SSN structure (for example, it does not reject `000-00-0000`). Pair with a dedicated validator if structural checks are needed. - Operates on the UTF-8 codepoint level. ## Compatibility - Matches the PG-compat MASK_SSN semantics used in PII-aware reporting pipelines.

Parameters

Name	Type	Description
`ssn`		Specifies the SSN string to mask. Hyphens in the standard XXX-XX-XXXX format are preserved; every digit except the last four is replaced with an asterisk.

Examples

-- Standard hyphenated format
SELECT MASK_SSN('123-45-6789');  -- '***-**-6789'

-- SSN without hyphens
SELECT MASK_SSN('123456789');  -- '*****6789'

-- Build a safe HR report
SELECT employee_id, MASK_SSN(ssn) AS ssn_display
FROM hr.catalog.employees;

-- Guard against invalid inputs
SELECT CASE WHEN LENGTH(REGEXP_REPLACE(ssn, '[^0-9]', '')) = 9 THEN MASK_SSN(ssn) ELSE '***' END AS masked
FROM hr.stage.raw_employees;

-- NULL propagation
SELECT MASK_SSN(NULL);  -- NULL

Pitfalls

MASK_SSN is a display transformation. Regulated SSN storage (for example, HIPAA or state-level PII rules) typically requires encryption at rest or tokenization, not masking.
The last four digits are often used as a 'last-four verification' token and are therefore still sensitive. Consider hashing the SSN for log correlation instead of relying on the last four digits.
The function does not validate the SSN. Special-case values such as `000-00-0000` or known-invalid ranges are masked without warning; validate upstream.
Masked SSNs with the same last four digits can be correlated across records. To prevent correlation in shared datasets, replace the trailing digits with a deterministic hash or a random token.