RegEx vs Character Loop

As is often the case with database management, we invariably have to clean up the data at some point. This demo was inspired by a thread discussion at UtterAccess where the original poster was looking for a way to remove unwanted characters from table records. (Here's the link to the UA thread, for additional information on the requirement.)

So, the requirement is easy enough, and as we all know in Access, there's always more than one way to reach a solution for any particular problem. That was also the case in this situation.

The two approaches I am comparing in this demo for removing unwanted characters from a string field in a table are (a) using regular expressions and (b) looping through the characters of the string. Both approaches will perform the job as required, but I was just curious if one particular approach would be faster than the other.

(click on the above image to download the demo file)

There may be other ways to test this theory other than the way I did it in this demo. For example, we could probably test the amount of time it would take to create a new table based on the result of cleaning up the data. As it stands right now, I just simply open and close a query containing a function call to each approach.

I encourage you to download the test file (it's only 526 KB) and try it out for yourself. Different configurations should give us different results. Please share your results if you do decide to give it a try and give us your conclusions as well.

I hope someone could find this little experiment somewhat useful in your projects or learning experience.

Thank you for reading...

UPDATE:
I wasn't sure if simply opening and closing the queries calling each function is enough to test for speed, so I added a couple more processes within each approach. You can download v1.1 using this link, if you want to see if it makes any difference in your outcomes. Cheers!

Extract Data Using Regular Expressions

Earlier, I wrote this article for extracting e-mail addresses from a memo field using regular expressions. What it was able to do is return all strings matching a particular pattern. Recently, I had a request to enhance the code to return all strings "inside" a particular pattern. See the discussion thread at UtterAccess. As a result, the following modified function takes a pattern and uses the Replace method to return only the string inside the "boundary" identified within the pattern.

Public Function ExtractData(strData As String, _
    Optional strDelim As String = ";") As String
'http://accessmvp.com/thedbguy
'2/9/2016
'Extracts data using regular expressions

Dim regEx As Object
Dim regExMatch As Object
Dim var As Variant
Dim strMatches As String

Set regEx = CreateObject("VBScript.RegExp")

With regEx
    .Global = True
    .IgnoreCase = True
    .Pattern = "\[(\w+)\]"
    Set regExMatch = .Execute(strData)
    For Each var In regExMatch
        strMatches = strMatches & strDelim & .Replace(var, "$1")
    Next
End With

ExtractData = Mid(strMatches, Len(strDelim) + 1)

Set regEx = Nothing
Set regExMatch = Nothing

End Function

To use the above function, make sure to replace the "pattern" identifying the boundaries and the string pattern you're trying to extract. For example, as discussed in the UtterAccess thread, the above function looks for a single word inside a pair of square brackets. You can also use the above function to extract the body of an HTML page by using the following pattern: "<body>(.+[\s\S]+)</body>".

I hope you find the above function useful. As usual, all feedback are welcome. Thank you!

Extract Email Address

This topic is really about Regular Expressions. If you do not know, Regular Expressions are text strings used as a search pattern for matching  a group of characters within a larger string. You might think of them as "wildcard" characters. However, they can do more than just match characters in a string because you can embed "rules" within the search pattern that the search engine will use to evaluate a match.

For example, if you use a wildcard character in an Access query, you might use something like this:

WHERE LastName Like "a*"

In this example, the "*" is a wildcard character that tells the database engine to match all records with last names beginning with the letter "a." But what if you wanted to return all records with last names that begin with the letter "a" or the letter "b?" You might use something like this:

WHERE LastName Like "a*" OR LastName Like "b*"

So far, so good. But what if you wanted all records with last names that begin with the letters "a" through "g?" You would have a very long WHERE clause if you continue with the above syntax.

Enter Regular Expressions

So, to help with that kind of situation, Access allows us to use limited forms of regular expressions in queries. For example, to return records with last names that begin with the letters "a" through "g," we can use the following criteria:

WHERE LastName Like "[a-g]*"

Much simpler, right? However, like I said earlier, the Access' database engine only gives us limited regular expression capabilities. To take full advantage of regular expressions, we'll need to use VBA and an external regular expression engine. Fortunately, there is a library that is available to us as a reference called "Microsoft VBScript Regular Expressions x.x"
To show how powerful regular expressions are, the following code example shows how it can be used to extract an email address from a Memo field. I used this example as a response to a question in the MSDN forums. The user wanted a way to extract the email address from a Memo field that contains data similar to the following:

Lastname: Doe
Firstname: John
Email address: john.doe@acme.com
Company: Acme Co.

First, a disclaimer: I don't claim to be an expert in regular expressions. In fact, I rarely use them. I just know that if the situation is right, they can be a very powerful tool to use. I would encourage you to learn more about regular expressons to enhance your applications.
Here's a code example that uses a regular expression object to look for an email address within a string.

Public Function ExtractEmailAddress(strData As String) As String
'http://accessmvp.com/thedbguy
'6/19/2015
'Extracts the first email address within a string
'regex email pattern source: http://www.regular-expressions.info/email.html

Dim regEx As Object
Dim regExMatch As Object

Set regEx = CreateObject("VBScript.RegExp")

With regEx
    .IgnoreCase = True
    .Pattern = "\b[0-9A-Z._%+-]+@[0-9A-Z.-]+.[A-Z]{2,4}\b"
    Set regExMatch = .Execute(strData)
    If regExMatch.Count > 0 Then
        ExtractEmailAddress = regExMatch(0).Value
    Else
        ExtractEmailAddress = "No email found!"
    End If
End With

Set regEx = Nothing
Set regExMatch = Nothing

End Function

Notes: The above code example uses late binding, and it will only return the first email address found, if any. To see a modified version of the above code that returns all email addresses found as a delimited string, please visit my website.

I hope this short article piques your interest into regular expressions, and perhaps, I will be learning a thing or two from you very soon. Thank you for reading.

Original post date: June 28, 2015