CODEWITHSUNDEEP
regex
Del
Del

Reputation: 21

Replacing Credit Card Numbers

I'm using an sql to replace credit card numbers with xxxx and finding that REGEX_REPLACE does not consistently replace everything. Below is the SET command i'm using on the SQL 

SET COMMENTS_LONG = 
    REGEXP_REPLACE (COMMENTS_LONG,'\D[1-6]\d{3}.\d{4}.\d{4}.\d{3}(\d{1}.\d{3})?|\D[1-6]\d{12,15}|\D[1-6]\d{3}.\d{3}.?\d{3}.\d{5}', ' XXXXXXXXXXXXXXXX')

Before

Elizabeth aclled to change address.5430-6000-2111-1931 A

After

Elizabeth aclled to change address XXXXXXXXXXXXXXXX1 A

I tried increasing the number of X but result is the same. I also find that i have to put a space in front of the first X as it appears to move 1 char to the left.

Upvotes: 2

Views: 3162

Answers (1)

Jürgen Steinblock
Jürgen Steinblock

Reputation: 31743

I would't make the regex to specific, that increases the change of accidentially letting real numbers that don't match your expression pass to the end user.

I would just use a simple regex like this:

(\d+-){3}\d+

btw: why did you include \D at the beginning? The . is not part of the creditcard number, right?

Edit: Just found this regex

\b(?:\d[ -]*?){13,16}\b

at this site: http://www.regular-expressions.info/creditcard.html

You should read the paragraph "Finding Credit Card Numbers in Documents"

Upvotes: 5

Related Questions