Find and Replace credit card numbers

Question

We have a large database with a lot of data in it. I found out recently our sales and shipping department have been using a part of the application to store clients credit card numbers in the open. We've put a stop to it, but now there are thousands of rows with the numbers.

We're trying to figure out how to scan certain columns for 16 digits in a row (or dash separation) and replace them with X's.

It's not a simple UPDATE statement because the card numbers are stored among large amounts of text. So far I've been unable to figure out if SQL Server is capable of regex (it would seem not).

All else fails i will do this through PHP since that is what i'm best at... but it'll be painful.

Answer 1

Sounds like you need to use PATINDEX with a WHERE LIKE clause.

Something like this. Create a stored proc with something similar, then call it with a bunch of different parameters (make @pattern & @patternlength the params) that you have identified, until you've replaced all of the instances.

declare @pattern varchar(100), @patternlength int
set @pattern = '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
set @patternlength = 19

update  tableName
set fieldName = 
    LEFT(fieldName, patindex('%'+ @pattern + '%', fieldName)-1) 
    + 'XXXX-XXXX-XXXX-XXXX' 
    + SUBSTRING(fieldName, PATINDEX('%'+ @pattern + '%', fieldName)+@patternlength, LEN(fieldName))
from tableName
where fieldName like '%'+ @pattern + '%'

The trick is just finding the appropriate patterns, and setting the appropriate @patternlength value (not the length of @pattern as that won't work!)

Answer 2

I think you are better off doing this programatically, especially since you mentioned the data can be in a couple of different formats. Do keep in mind that not all credit card numbers are 16 digits long (Amex is 15, Visa is 13 or 16, etc).

The ability to check for various regexes and validate code will probably be best served at a cleanup job level, if possible.