MASK_PHONE

Redact a phone number for display while preserving the last four digits.

Category: miscReturns: STRINGDialect: PostgreSql

Syntax

MASK_PHONE(phone)

Description

## Overview Returns a redacted version of the input phone number suitable for display in logs, audit trails, and operator UIs. Every digit except the last four is replaced with an asterisk; non-digit characters (spaces, plus signs, hyphens, parentheses) are preserved in place so the formatting remains recognizable. Use this function whenever phone numbers must be shown without exposing the full number. MASK_PHONE is a display-only redaction. ## Masking pattern - Digits are replaced with `*` except for the last four, which are preserved verbatim. - Non-digit characters (including `+`, `-`, space, `(`, `)`) 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 phone number shape. Any string is accepted; the formatting is preserved. - Operates on the UTF-8 codepoint level. ## Compatibility - Matches the PG-compat MASK_PHONE semantics used in PII-aware reporting pipelines.

Parameters

Name	Type	Description
`phone`		Specifies the phone number string to mask. Punctuation and whitespace are preserved in place; each digit is inspected individually.

Examples

-- US-style phone number
SELECT MASK_PHONE('+1-555-123-4567');  -- '+*-***-***-4567'

-- Number with spaces and parentheses
SELECT MASK_PHONE('(555) 123-4567');  -- '(***) ***-4567'

-- International format
SELECT MASK_PHONE('+44 20 7946 0958');  -- '+** ** **** 0958'

-- Build a safe contact display
SELECT customer_id, MASK_PHONE(phone) AS phone_display
FROM crm.catalog.customers;

-- NULL propagation
SELECT MASK_PHONE(NULL);  -- NULL

Pitfalls

The function preserves the country code and formatting, which can still identify a region or carrier. For stronger anonymization, consider hashing with SHA256 or replacing the entire value with a fixed token.
Extensions (for example, `x1234` after the main number) have their digits treated the same way as any other digit: if they fall in the last four, they remain visible. Normalize the input if you want to avoid exposing extension digits.
The mask preserves input length, which can itself be PII (a 15-digit international number is clearly not a 10-digit US number). Use a fixed-format replacement if length must be redacted.
No validation is performed. A malformed phone string is returned with its digits masked but its shape intact; pair with a normalization step if consistency across rows matters.