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 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:
select n.*, x.*
from tx_cust_notes n
JOIN tx_cust_notes_ext x ON n.cust_notes_no = x.cust_notes_no
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
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).
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!