Skip to main content

Using Regular Expressions in IDEAScript and Custom Functions

Regular Expressions or regexp is a powerful language that allows you to search for word patterns or replace word patterns with another pattern.  It is so powerful that it can be considered a language onto itself.

Microsoft defines it as "Regular expressions provide tools for developing complex pattern matching and textual search-and-replace algorithms."

This VBA functionality is available in IDEAScript and Custom Functions.  The following is some example code on how to access it that I obtained from the CaseWare IDEA web site.  I am hoping to have a series of posts explaining the different functionality and how to use it as it is a very powerful feature that can be quite useful.

Dim RE As Object
Dim REMatches As Object
Dim re6 As String
Set RE = CreateObject("vbscript.regexp")
RE.MultiLine = False
RE.Global = False
RE.IgnoreCase = True
RE.Pattern = reg_exp
    
Set REMatches = RE.Execute(input_string)
    
If rematches.count = 0 Then Exit Function
    
RE6 = REMatches(0)

In the above code you first create an object to hold the Regular Expressions object and the match results from the Regular Expressions object. 

As regexp is not a standard in the IDEAScripting language you have to use the CreateObject to obtain the regexp functionality.

MultiLine allows for searchs over more than one line,

Global allows you to do replacement over the entire database instead of just the first instance.

IgnoreCase, I think this is fairly obvious, it will ignore any case differences.

Pattern is the expression that you are looking for or replacing.  These expression can be quite complex but also you can use it to validate information such as email addresses, urls or pretty much anything.

Finally you perform the search on the string or field and it will return the number of matches.

Crypto Mon, 11/27/2017 - 10:40

 
 
 
Hello !
Where you able to use it in IDEA ?
I tried to integrate it for pattern matching like social security number but wasn't able to make it work even with different tries and easier pattern to test the function.
Quote: The following is some example code on how to access it that I obtained from the CaseWare IDEA web site
Is there still any info available on the Caseware website, because I didn't find anything about it ?
Or do you have any working example I can adapt to my needs ?
 
Thank you, have a nice day !
 
 
 
 

Brian Element Fri, 12/01/2017 - 08:07

Hi Crypto,

If you want to use this as part of the equation editor there is a function called @RegExpr that you can use.  There is also a sight called Regular Expression Library that is great for finding different types of patterns to match (http://regexlib.com/).

Here are some common expressions showing how to use them in IDEA:

emails - @RegExpr(EMAILS, "^\w+@[a-zA-Z_]+?\.[a-zA-Z]{2,3}$")  (http://regexlib.com/Search.aspx?k=email)

phone number - @RegExpr(PHONE, "^[2-9]\d{2}-\d{3}-\d{4}$") (http://regexlib.com/Search.aspx?k=phone)

Canadian postal codes (I use the strip as it may have a space in it) - @RegExpr(@Strip(POSTAL_CODE), "^([A-Za-z]\d[A-Za-z][-]?\d[A-Za-z]\d)") (http://regexlib.com/Search.aspx?k=canadian+postal+code)

Hopefully this helps you out a bit.  The code above is if you want to access this through a custom function or an IDEAScript.

Brian

Alfin Tue, 04/16/2024 - 06:12

Hi Brian,
What is the opposite of @RegExpr? like if I want to retrieve all the rows that don't contain a particular word. Is there a particular symbol that can make my @RegExpr result negative or the opposite?
Regards,

Brian Element Tue, 04/16/2024 - 10:53

In reply to by Alfin

Make the @RegExpr equal to a blank.  So for the phone numbers you would have @RegExpr(PHONE, "^[2-9]\d{2}-\d{3}-\d{4}$") = "" and that should return all the lines in which the phone number expression does not match.