I have a 16 digit card number where I need to replace the 3rd digit of the card number to 9th digit card number with *.Tried regular expressions,replace and translate nothing worked.Need guidance in this tom!!
First up, I hope you're not storing the card number as-is. If so you're in violation of PCI standards:
3.4 Render PAN, at minimum, unreadable anywhere it is storeSee page 14 of
https://www.pcisecuritystandards.org/documents/pci_ssc_quick_guide.pdf Also: your requested masking pattern violates their rules:
3.3. Mask PAN when displayed; the first six and last four digits are the maximum number of digits
you may displayBearing this in mind...
If you have the Advanced Security option this is easy: use Data Redaction.
create table cards (
card_number varchar2(20)
);
grant create session to u
identified by u;
grant select on cards to u;
insert into cards values ( '1234-1234-1234-1234' );
commit;
begin
dbms_redact.add_policy(
object_schema => user,
object_name => 'cards',
column_name => 'card_number',
policy_name => 'redact_sensitive',
function_type => dbms_redact.partial,
function_parameters => 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,3,9',
expression => '1 = 1'
);
end;
/
conn u/u
select * from chris.cards;
CARD_NUMBER
12**-****-*234-1234
If you don't have this, a little regular expression will do the trick. The following finds a string starting with two digits, followed by a series of nine digits or hyphens:
^([0-9]{2})([0-9-]{9})
Which in a complete SQL statement is:
select regexp_replace (
card_number,
'^([0-9]{2})([0-9-]{9})',
'\1**-****-*'
) masked_number
from cards;
MASKED_NUMBER
12**-****-*234-1234
But beware: this assumes 16-digit numbers, separated by hyphens. You may receive card numbers in other formats, e.g. just 16 digits without hyphens. Or other separators such as spaces.
And if your business accepts AMEX as payment, these only have 15-digits, with the separators in different places.
So you'll need to adjust the regex or redaction rules depending on how exactly you receive the card numbers.