mysql REGEXP exact pattern match

Question

I'm working on writing a php script that will loop through all tables in database and look for plain text credit card numbers. For this I'm using MySQL REGEXP. I need only those results which "only" contains credit card numbers. If there is any textual data that contains credit card number then the script should not consider it.

e.g.

|   id   |        ccno        |
|   1   |        4111111111111111        |
|   2   |        4111 1111 1111 1111    |
|   3   |        4111-1111-1111-1111   |
|   4   | some text 4111111111111111 some text |

If the query is trieggered on above table then it should return first 3 records, it should not return 4th record. For this purpose I'm using below query and the issue I'm facing is the query is returning all the 4 records.

SELECT ccno FROM aacc WHERE 
(ccno = REGEXP '[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]'
OR ccno = REGEXP '[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]'
OR ccno = REGEXP '[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]')

Don't you also want to verify the "check digit"?

Rick James
– Rick James

2018-10-04 20:59:42 +00:00
Commented Oct 4, 2018 at 20:59 — Rick James
– Rick James, Commented Oct 4, 2018 at 20:59

Tim Biegeleisen · Accepted Answer · 2018-09-27 13:22:45Z

2

There are known complex regular expressions to match card numbers from the large credit card providers, such as Visa and MasterCard. For the purpose of your question, the following pattern comes close to what you want:

SELECT *
FROM yourTable
WHERE ccno REGEXP '^[0-9]{4}([[:space:]-]?[0-9]{4}){3}$';

Demo

This pattern is not exact, but then again none of your data matches, for example, Amex cards, which have the format of dddd-dddddd-ddddd, with two dashes, not three. If you plan on doing this with production data, you should research how to match credit card numbers with regex.

https://www.regular-expressions.info/creditcard.html

edited Sep 27, 2018 at 13:22

answered Sep 27, 2018 at 13:17

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nambi_0915 · Accepted Answer · 2018-09-27 13:32:19Z

2

Try regex ^([0-9]{4}[-[:space:]]?){4}$

This will match the 16 numbers separated by space or hyphen optionally

regex

edited Sep 27, 2018 at 13:32

answered Sep 27, 2018 at 13:11

Nambi_0915

1,0998 silver badges22 bronze badges

3 Comments

Suyog Over a year ago

This is working fine for 16 digits but how to match 15 digit cards?

Tim Biegeleisen Over a year ago

@Suyog If you have a serious need to validate credit numbers, then should read here; this is a problem which has already been tackled many times already.

Suyog Over a year ago

Yes, Thanks @ran_0315

Suyog · Accepted Answer · 2018-09-28 10:45:38Z

Though above two answers helped me in what I was looking for, I've now wrote entire query that will give exact match for plain text credit cards. Cards that are working fine for this query are:
American Express
Visa
MasterCard
Discover

The MySQL query is:

SELECT *
FROM yourTable
WHERE (ccno REGEXP '^4[0-9]{3}([[:space:]-]?[0-9]{4}){3}$'
       OR ccno REGEXP '^3[47][0-9]{2}[[:space:]-]?[0-9]{6}[[:space:]-]?[0-9]{5}$'
       OR ccno REGEXP '^6011([[:space:]-]?[0-9]{4}){3}$'
       OR ccno REGEXP '^65[0-9]{2}([[:space:]-]?[0-9]{4}){3}$'
       OR ccno REGEXP '^5[1-5][0-9]{2}([[:space:]-]?[0-9]{4}){3}$'
       OR ccno REGEXP '^222[1-9]([[:space:]-]?[0-9]{4}){3}$'
       OR ccno REGEXP '^2[3-6][0-9]{2}([[:space:]-]?[0-9]{4}){3}$'
       OR ccno REGEXP '^27[01][0-9]([[:space:]-]?[0-9]{4}){3}$'
       OR ccno REGEXP '^2720([[:space:]-]?[0-9]{4}){3}$'
       );

Working example can be seen here.

Collectives™ on Stack Overflow

mysql REGEXP exact pattern match

3 Answers 3

Demo

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related