SQL Server – Retrieve 16 Digit Card Numbers from a String

sql servert-sql

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.

Best Answer

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             |
+------------------+
Related Question