I have a memory of a presenter several conferences ago mentioning having a function that searches notes fields for things that are probably credit card numbers and either removes them? or fixes them to only show the last four? or rat's out the person whose been improperly storing credit cards? I forget exactly what. But if you are that presenter, or someone who has such a function, would you mind sharing it with me?
I'm not that presenter, but the simple ratting-out version would probably go a little something like this:
[my first try, which then I thought better of and edited:]
The only thing would be if the card number happened to break across two pieces of the notes field (you know how they're stored in 255 character chunks).
Here's a version that goes across note boundaries...
-- go across 255 character chunk boundaries
select n.*, x.*
from tx_cust_notes n
JOIN tx_cust_notes_ext x ON n.cust_notes_no = x.cust_notes_no
LEFT OUTER JOIN tx_cust_notes_ext x2 ON n.cust_notes_no = x2.cust_notes_no AND x.serial_order +1 = x2.serial_order
where x.notes + isnull(x2.notes, '') like '%[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 x.notes + isnull(x2.notes, '') like '%[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 x.notes + isnull(x2.notes, '') like '%3[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]%' -- amex format
OR x.notes + isnull(x2.notes, '') like '%3[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]%' -- amex format no dash