How to mask credits card in sql?

Question

I need to do an update on a table that has over 1 million records to mask the credit cards.

I have to keep the last 4 digits and the rest should become X's.

I come up with this so far

BEGIN TRAN
-- SQL update in batches of 10,000
WHILE (2 > 1)
  BEGIN
    BEGIN TRANSACTION
    UPDATE TOP ( 10000 ) tblMyTable
    SET creditCardNumber = 'XXXXXX' + RIGHT(creditCardNumber, 4)
   WHERE myDate < '2010-Feb-02'

    IF @@ROWCOUNT = 0
      BEGIN
        COMMIT TRANSACTION
         BREAK
      END
    COMMIT TRANSACTION
    -- 1 second delay
    WAITFOR DELAY '00:00:01'
  END -- WHILE
GO
ROLLBACK

The problem with this though is there are records in the database that are obviously fake credit cards and may not even have 4 digits. Basically what I need to update this to is this add the same number of X's and keep the last 4 digits. If there are less than 4 digits then add some more X's.

Example

1242 would turn into XXXXXX1242
12 would turn into   XXXXXXXX12 
1234567890 would turn into  XXXXXX7890

@MikeChristensen LOL. I think we can find plenty of russian sites that would help for free! — Bohemian, Jan 27 '12 at 00:18
wonder if you can use substring and achieve that. for example create maximum number of 'xxxx' string. and than use length and substring add to rihgt(creditcardnumber, 4). what you think? — AJP, Jan 27 '12 at 00:23

score 4 · Answer 1 · edited May 23 '17 at 12:25

4

If the only problem is some numbers are less than four characters, left pad it?

see Most efficient T-SQL way to pad a varchar on the left to a certain length?

for an explanation of how

right('XXXXXXXXXXXX'+ @str, @n)

works.

edited May 23 '17 at 12:25

Community

1
1

answered Jan 27 '12 at 00:19

justin cress

1,745
5
24
35

I think this will be my only problem. I am still not sure if it is in a end-less loop. I ran it and it seemed to go over the number of rows I was told there would be. I am actually unsure how IF @@ROWCOUNT = 0 gets satisfied to get out of the loop. – chobo2 Jan 27 '12 at 16:56

AJP · Accepted Answer · 2017-11-16T22:32:52.130

2

Declare @xx VARCHAR(20)
DECLARE @length INT

SET @length = 17
SET @xx = 'XXXXXXXXXXXXXXXX'

UPDATE TOP ( 10000 ) tblMyTable 
    SET creditCardNumber = SUBSTRING(@xx, 0, @length - len(RIGHT(creditCardNumber, 4))) + RIGHT(creditCardNumber, 4) 
   WHERE myDate < '2010-Feb-02'

see if this solves it.

edited Nov 16 '17 at 22:32

answered Jan 27 '12 at 00:36

AJP

2,125
3
16
22

score 1 · Answer 3 · edited Oct 18 '13 at 12:48

1

select
    stuff(account_no,1,len(account_no)-4,REPLICATE('X', LEN(account_no)-4)),
    account_no,
    stuff(routing_no,1,len(routing_no)-3,REPLICATE('X', LEN(routing_no)-3)),
    routing_no
from dbo.PAYER_BANK_DETAIL

edited Oct 18 '13 at 12:48

Raidri

17,258
9
62
65

answered Oct 18 '13 at 12:28

user2894725

11
1

How to mask credits card in sql?

3 Answers3