Retrieve 16 digit card numbers from a string
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             |
+------------------+