Michael Bahr
jedi at charm.net
Sat Apr 19 17:48:44 CDT 2008
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