aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
fn_mask_credit_card
Parameters
Name
Type
Mode
p_value
text
IN
p_mask_format
text
IN (DEFAULT NULL)
Definition
DECLARE v_fmt text := coalesce(p_mask_format, fn_prop_config('CREDIT_CARD_MASK', 'NNNNNN******NNNN')); v_prefix int := coalesce(length((regexp_match(v_fmt, '^[Nn]*'))[1]), 0); -- leading N = keep first N digits v_suffix int := coalesce(length((regexp_match(v_fmt, '[Nn]*$'))[1]), 0); -- trailing N = keep last N digits v_token text; v_digits text; v_len int; v_result text := p_value; BEGIN IF p_value IS NULL OR p_value = '' THEN RETURN p_value; END IF; -- Find every card-like token: 13-19 digits, optionally separated by space/dash. -- Already-masked values (digits broken up by '*' / 'X' / '#') never match, so -- masking is idempotent and safe over data masked manually beforehand. FOR v_token IN SELECT m[1] FROM regexp_matches(p_value, '(\d(?:[ -]?\d){12,18})', 'g') AS m LOOP v_digits := regexp_replace(v_token, '\D', '', 'g'); v_len := length(v_digits); IF v_len BETWEEN 13 AND 19 THEN IF v_prefix + v_suffix >= v_len THEN v_result := replace(v_result, v_token, repeat('*', v_len)); -- degenerate format => mask fully ELSE v_result := replace(v_result, v_token, left(v_digits, v_prefix) || repeat('*', v_len - v_prefix - v_suffix) || right(v_digits, v_suffix)); END IF; END IF; END LOOP; RETURN v_result; END;