MASK_CREDIT_CARD

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

Category: miscReturns: STRINGDialect: PostgreSql

Syntax

MASK_CREDIT_CARD(card)

Description

## Overview Returns a redacted version of the input credit card number suitable for display in logs, audit trails, and operator UIs. Every digit except the last four is replaced with an asterisk; non-digit separators such as spaces and hyphens are preserved in their original positions. Use this function wherever PAN data must be shown without exposing the full account number. MASK_CREDIT_CARD is a display-only redaction and is not a substitute for PCI-compliant tokenization. ## Masking pattern - Digits are replaced with `*` except for the last four, which are preserved verbatim. - Non-digit characters (spaces, hyphens, slashes) 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 card number structure or checksum. Combine with LUHN_CHECK if validation is required. - Operates on the UTF-8 codepoint level. ## Compatibility - Matches the PG-compat MASK_CREDIT_CARD semantics used in PCI-oriented reporting pipelines.

Parameters

Name	Type	Description
`card`		Specifies the credit card number string to mask. Spaces and hyphens are preserved in place; each digit is inspected individually.

Examples

-- Typical 16-digit card with no separators
SELECT MASK_CREDIT_CARD('4539578763621486');  -- '************1486'

-- Card with spaces preserved
SELECT MASK_CREDIT_CARD('4539 5787 6362 1486');  -- '**** **** **** 1486'

-- Card with hyphens preserved
SELECT MASK_CREDIT_CARD('4539-5787-6362-1486');  -- '****-****-****-1486'

-- Build a safe display column in a payments dashboard
SELECT payment_id, MASK_CREDIT_CARD(card_number) AS card_display
FROM payments.catalog.transactions;

-- Chain with LUHN_CHECK to only display numbers that passed checksum validation
SELECT CASE WHEN LUHN_CHECK(REGEXP_REPLACE(card, '[^0-9]', '')) THEN MASK_CREDIT_CARD(card) END AS masked
FROM payments.stage.raw_cards;

-- NULL propagation
SELECT MASK_CREDIT_CARD(NULL);  -- NULL

Pitfalls

Masking is for display only. Storing the masked form alongside the original PAN does not reduce PCI scope. Use a dedicated tokenization service for compliance.
The function preserves separators in place. If your input has inconsistent formatting (sometimes spaces, sometimes hyphens), the mask width will vary between rows. Normalize before masking if uniform output matters.
Cards shorter than five digits have every digit redacted. Do not rely on MASK_CREDIT_CARD to expose any trailing digits when the input is truncated.
The function does not enforce BIN length or brand. A 12-digit string is masked the same way as a 16-digit string; validate shape upstream.