Mark A Matte
markamatte at hotmail.com
Sun Apr 20 16:04:15 CDT 2008
Mike,
Thanks very much. I'm not familiar with "regular expressions"...but curious. I will be out of town for a week...but will implement next week.
Thanks again,
Mark A. Matte
> Date: Sat, 19 Apr 2008 18:48:44 -0400
> From: jedi at charm.net
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] Search for credit card numbers
>
> Hi Mark, here is an improved function to capture cc numbers within a
> string. The other one I sent you only worked 2/3, the contiguous part
> failed by not capturing enough. However this version fixes that. I have
> included instructions within the function so please follow them. Also keep
> this function handy somewhere and use it as a template for future regular
> expressions.
>
> Explanation:
> This function only demonstrates the power of regular expressions and
> includes a built-in test string. All you need to do is copy it and paste
> into a module and run it.
>
> I modified the test pattern to
> ccpattern = "(\d{3,5}((\ |-|\.)\d{3,5})+|\d{13,16})"
> and is in 2 parts.
>
> Part 1, \d{4,6}((\ |-|\.)\d{4,6})+ includes some enhancements. The \d{4,6}
> means that I am looking for either 4,5,6 digits-no more, no less. I
> removed the ? from the (\ |-|\.) because now I want to implicitly look for
> groups of
> ((\ |-|\.)\d{4,6})+ like .12345. So now I will capture numbers like
> 1234-567899-90001 and 1234 0001.1000-9999 and so on. Btw, you always
> modify the
> (\ |-|\.) to suit your needs adding more simply by inserting a | between
> each char. Notice that some chars have a \ in front-that just means to use
> the literal char as some of these chars have special meanings.
>
> Part 2, |\d{13,16} is new in that this is an OR condition that uses the "|"
> pipe or vertical bar as the OR part. So now I am implicitly looking for
> 13,14,15,16 contiguous digits.
>
> When you ready to implement this into your project copy the guts into your
> code.
>
> Enjoy, Mike...
>
>
> '
> ' set a debug breakpoint at the regex.test(str) and
> ' step 1 line at time
> '
> ' in the debug window add watches for the following:
> ' matches
> ' matches(0).submatches
> ' matches(1).submatches
> ' matches(2).submatches
> ' matches(3).submatches
> ' matches(4).submatches
> ' ccnumber
> '
> ' these will contain the cc number if matched
> '
> Function ccMatch() As Boolean
> Dim regex As Object
> Dim ccpattern As Variant
> Dim regexMatch As Boolean
> Dim str As String
> Dim matches As Variant
>
> str = "The quick brown fox with cc numbers 1234 0001.1000-9999,
> 1234-567899-90001, 123.100000-99999, 1234567890123, and 0000999910009999
> jumped over the fence"
>
> ccMatch = 0
> ccpattern = "(\d{4,6}((\ |-|\.)\d{4,6})+|\d{13,16})"
> ' ccpattern = "(\d+((\ |-|\.)?\d+)+)"
>
> Set regex = CreateObject("VBScript.RegExp")
> ' global is needed for multiple matches otherwise
> ' will stop after first match
> regex.Global = True
> ' regexMatch = True
> ' regex.ignorecase = True
>
> ' test for cc number
> regex.pattern = ccpattern
> regexMatch = regex.test(str)
> Set matches = regex.Execute(str)
>
> ' stop stepping here and look at the results
> If (regexMatch) Then
> ' got a match
>
> ' the matches collection show how many matches there are in the
> string
> ' the submatches index is the parenthesis order starting
> ' from left to right, in debug these are shown as item 1, item 2, etc
> ' and tell you how to numerate the collection starting with 0
>
> ' iterate through collection
> For Each thisitem In matches
> ccnumber = thisitem
> ' now you can use your best method for replacing the cc number
> ' with your string
> ' regex.replace
> Next
>
> ccMatch = 1
> End If
> End Function
>
>
>
>
> *********** REPLY SEPARATOR ***********
>
> On 4/18/2008 at 8:41 PM Michael Bahr wrote:
>
>>Ok here is a function that may work for you. I have not tested it. I
>>leave the replace part to you. Use the debugger amply.
>>
>>' This will match the following cc numbers:
>>'0000-0000-0000-0000
>>'0000 0000 0000 0000
>>'0000000000000000
>>
>>'
>>' set a debug breakpoint at the top of this function and
>>' step 1 line at time
>>'
>>Function ccMatch (str as String) As Boolean
>> Dim regex As Object
>> Dim ccpattern as Variant
>> Dim regexMatch As Boolean
>> Dim matches As MatchCollection
>>
>> ccMatch = 0
>> ccpattern = "(\d+((\ |-)?\d+)+)"
>>
>> Set regex = CreateObject("VBScript.RegExp")
>> regexMatch = True
>> regex.Global = True
>> regex.ignorecase = True
>>
>> ' test for cc number
>> regex.pattern = ccpattern
>> regexMatch = regex.test(str)
>>
>> If (regexMatch) Then
>> ' got a match now substitute your string
>> ' matches(0).submatches(0) should contain the cc number
>>
>> Set matches = regex.Execute(str)
>>
>> ' once you step here look at the properties for the matches object
>> '
>> ' the submatches index is the parenthesis order starting
>> ' from left to right--can be viewed in the debug window
>> ' while manually stepping
>> ccnumber = matches(0).submatches(0)
>>
>> ' now you can use your best method for replacing the cc number
>> ' with your string
>> ' regex.replace
>>
>> ccMatch = 1
>> End If
>>
>>End Function
>>
>>hth. Gotta go, Dr. Who is on and then BSG. SciFi Friday.
>>
>>Mike...
>>
>>
>>*********** REPLY SEPARATOR ***********
>>
>>On 4/18/2008 at 6:12 PM Mark A Matte wrote:
>>
>>>Thanks Mike,
>>>
>>>I had already considered the different lengths and seperators... this
>>>,"(\d+((\ |-)?\d+)+)", intrigues me...what do I do with it/how do i call
>>>it??? The numbers I'm looking for are in the middle of the text of a
> MEMO
>>>field.
>>>
>>>Thanks again,
>>>
>>>Mark A. Matte
>>>
>>>
>>>> Date: Fri, 18 Apr 2008 12:32:17 -0400
>>>> From: jedi at charm.net
>>>> To: accessd at databaseadvisors.com
>>>> Subject: Re: [AccessD] Search for credit card numbers
>>>>
>>>> Hi Mark, your best solution is to use a regular expression to match a
>>>> pattern of numbers. CC numbers can have dashes or spaces separating the
>>>> number groups or can be contiguous--just depends on how it was written.
>>>> For example using a pattern like
>>>>
>>>> (\d+((\ |-)?\d+)+)
>>>>
>>>> Meaning:
>>>> 1. The first set of parens (outer) capture the whole matching CC
> number.
>>>> 2. The first \d+ means one or more digits
>>>> 3. The ((\ |-)?\d+) means the next char either a space or "-" may exist
>>>> (because of the ?) and then again 1 or more digits
>>>> 4. And the final + is one or more of the pattern in #3.
>>>>
>>>> would cover 3 possible patterns
>>>> 0000-0000-0000-0000
>>>> 0000 0000 0000 0000
>>>> 0000000000000000
>>>>
>>>> CC number length range from 13 to 16 digits so this should catch them.
>>>> however this is not foolproof. If you happen to have some other number
>>>> that matches that pattern then it too will get caught.
>>>>
>>>> If you want to determine the issuer then a simple case statement
>>matching
>>>> the identifier will suffice.
>>>>
>>>> Give this some thought.
>>>>
>>>> Mike...
>>>>
>>>>
>>>>>
>>>>> Hello All,
>>>>>
>>>>> I'm getting ready to build something that searches MEMO and text
> fields
>>>>> for credit card numbers (card number NOT known)...and then replace
>>them.
>>>>>
>>>>> Before I got started, I was wondering if anyone has any advice...or
> has
>>>>> done somthing similar.
>>>>>
>>>>> Thanks,
>>>>>
>>>>> Mark A. Matte
>>>>> _________________________________________________________________
>>>>> Use video conversation to talk face-to-face with Windows Live
>>Messenger.
>>>>>
>>>http://www.windowslive.com/messenger/connect_your_way.html?ocid=TXT_TAGL
> M
>>_WL_Refresh_messenger_video_042008
>>>>> --
>>>>> AccessD mailing list
>>>>> AccessD at databaseadvisors.com
>>>>> http://databaseadvisors.com/mailman/listinfo/accessd
>>>>> Website: http://www.databaseadvisors.com
>>>>>
>>>>
>>>>
>>>> --
>>>> AccessD mailing list
>>>> AccessD at databaseadvisors.com
>>>> http://databaseadvisors.com/mailman/listinfo/accessd
>>>> Website: http://www.databaseadvisors.com
>>>
>>>_________________________________________________________________
>>>Pack up or back up–use SkyDrive to transfer files or keep extra copies.
>>>Learn how.
>>>http://www.windowslive.com/skydrive/overview.html?ocid=TXT_TAGLM_WL_Refr
> e
>>sh_skydrive_packup_042008
>>>--
>>>AccessD mailing list
>>>AccessD at databaseadvisors.com
>>>http://databaseadvisors.com/mailman/listinfo/accessd
>>>Website: http://www.databaseadvisors.com
>>
>>
>>
>>
>>
>>--
>>AccessD mailing list
>>AccessD at databaseadvisors.com
>>http://databaseadvisors.com/mailman/listinfo/accessd
>>Website: http://www.databaseadvisors.com
>
>
>
>
_________________________________________________________________
More immediate than e-mail? Get instant access with Windows Live Messenger.
http://www.windowslive.com/messenger/overview.html?ocid=TXT_TAGLM_WL_Refresh_instantaccess_042008