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
I used to do something similar using the scalar function FS_REMOVE_NON_NUMERICS(yourTextColumn), which would allow me to spot check and find ones entered like XXX XXXX XXXX XXXX, and XXXX-XXXX-XXXX-XXXX.
Also, constituent notes can be concatenated for each note using the function FS_GET_CUST_NOTES(cust_notes_no) in a column of the query:
Select n.customer_no,
n.cust_notes_no,
notes = [dbo].FS_GET_CUST_NOTES(n.cust_notes_no),
notes_length = len([dbo].FS_GET_CUST_NOTES(n.cust_notes_no))
From [dbo].VXS_CUST_NOTES n
Where n.customer_no = 7
+Ryan Creps
+Tessitura Network
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Andrea CrainSent: Thursday, December 15, 2011 9:21 PMTo: Ryan CrepsSubject: Re: [Tessitura Technical Forum] Find Credit Cards in Notes
I'm not that presenter, but it would probably go a little something like this:
where x.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 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 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 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
From: Amanda Freeman <bounce-amandafreeman5080@tessituranetwork.com>Sent: 12/14/2011 4:12:17 PM
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
Here’s a simple example using CSI notes:
Select customer_no,
notes
From [dbo].T_CUST_ACTIVITY
Where len(dbo.FS_REMOVE_NON_NUMERICS(notes)) > 14
This simply finds any note with at least 14 numbers “somewhere” in the note. Add filters and change objects to taste. Rinse and repeat.
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ryan CrepsSent: Friday, December 16, 2011 2:37 PMTo: Ryan CrepsSubject: RE: [Tessitura Technical Forum] Find Credit Cards in Notes