As a few people have mentioned, this is fairly easy to accomplish with a Regular Expression. The following pattern identifies the variations found in your data with respect to being constrained to just Visa, MasterCard, and AMEX (the frowny face is a colon followed by a left-parenthesis):

\b(?:(?:\d{4}(?:[ -]?\d{4}){3}|\d{4}[ -]?\d{6}[ -]?\d{5})(?=\d{2}/\d{2}|[^/0-9]|$)|\d{16,}(?=[^/0-9]|$))

What that pattern looks for is:

• MATCH starts on a word boundary (i.e. no leading digits so it won't capture the last 16 digits of a 20 digit sequence!)

[h2]-- AND --[/h2]

(handle CC numbers)

• MATCH contains:

  4 digits followed by three sets of "0 or 1 instances of a space or dash followed by 4 digits" (this covers all 16-digit variations)

  OR

  4 digits, followed by 0 or 1 instances of a space or dash, followed by 6 digits, followed by 0 or 1 instances of a space or dash, followed by 5 digits (this covers all 15-digit variations)

• PATTERN requires but does not include in MATCH:

  MATCH followed by one of these three patterns:

  * 2 digits followed by "/" followed by 2 digits

  * any character that is not a "/" or digit

  * end of string

[h3]-- OR -- [/h3]

(handle transaction codes)

• MATCH contains 16 or more digits
• PATTERN requires but does not include in MATCH:

  MATCH followed by one of these two patterns:

  * any character that is not a "/" or digit

  * end of string

Hence, this approach does actually extract the CC number, regardless of formatting, regardless of there being an expiration date following the CC number.

This pattern also prevents matching invalid sequences such as an AMEX number that is missing a digit (hence 14 digits) that is followed by an expiration date, which starts with 2 digits for a total of 16 digits, and hence can be a false-positive for any of the three card types assuming no expiration date.

The example below shows this behavior. The example uses a RegEx function, RegEx_MatchSimple4k, that is available in the free version of SQL#[/url], a library of SQLCLR functions that I wrote.

DECLARE @test-2 TABLE ([memo] VARCHAR(400) );

INSERT INTO @test-2 ([memo])

SELECT 'abc this account used visa :1234567890123456' UNION ALL

SELECT 'this account visa 1234567890123456 request for receipt' UNION ALL

SELECT 'this account visa 123456789012345612/13 exp' UNION ALL

SELECT 'invoice card used 4123 4567 8901 2345 some text' UNION ALL

SELECT 'paid using 4123 4567 8901 2345 thank you' UNION ALL

SELECT 'Transaction code 123456454636667445 copy invoice'UNION ALL -- this is 18 digits, not 16!

SELECT 'mastercard cc#5987-6543-2109-8765. this order will be processed' UNION ALL

SELECT 'mastercard 5987-6543-2109-8765, thank you' union all

SELECT 'AMEX code 123454636667445 copy invoice'UNION ALL -- AMEX with no spacers

SELECT 'AMEX two 1234 546366 67445 copy invoice'UNION ALL -- AMEX with spacers

SELECT 'AMEX with exp date 12345463666744599/12 copy invoice'UNION ALL -- AMEX with no spacers + exp

SELECT 'another AMEX with exp date 1234 546366 6744599/12 copy invoice'UNION ALL -- AMEX with spacers + exp

SELECT 'AMEX missing 1 digit 1234567890123412/13 exp' UNION ALL -- INVALID, but 16 digits before the "/"

SELECT 'tran code 1234567890123412234234213' UNION ALL

SELECT 'any card 123456789012341223423421345/45 exp' UNION ALL -- INVALID, too long

SELECT ' period 1999-10-18 - -2000-10-17 ';

DECLARE @Pattern NVARCHAR(120);

SET @Pattern = N'\b(?:(?:\d{4}(?:[ -]?\d{4}){3}|\d{4}[ -]?\d{6}[ -]?\d{5})(?=\d{2}/\d{2}|[^/0-9]|$)|\d{16,}(?=[^/0-9]|$))';

SELECT [memo], SQL#.RegEx_MatchSimple4k([memo], @Pattern, 1, NULL) AS [CC]

FROM @test-2;