1

I have a large database table, which contains records of users accounts. Back in 2011 a lot of notes were added to the accounts, which contained the full debit card details.

How can I find all 16 digit card numbers in the table? They are all entered differently.

0
3

Assuming a record contains no more than a single 16+ digits number

select  substring (mycol,nullif(patindex('%[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]%',mycol),0),16) as credit_card
from    mytable

Demo

create table mytable (mycol varchar (100));

insert into mytable (mycol) values 
    ('always look on the bright 9182738571098473 side of life')
   ,('Nobody expects the Spanish Inquisition!')
;

select  substring (mycol,nullif(patindex('%[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]%',mycol),0),16) as credit_card
from    mytable

+------------------+
| credit_card      |
+------------------+
| 9182738571098473 |
+------------------+
| NULL             |
+------------------+
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.