Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Praveen.

Asked: March 05, 2019 - 7:03 am UTC

Last updated: March 05, 2019 - 11:19 am UTC

Version: oracle sql developer

Viewed 1000+ times

You Asked

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!!

and Chris said...

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 store

See 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 display


Bearing 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.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.