Using Regular Expressions in IDEAScript and Custom Functions
Forums
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.
Hi Crypto,
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
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 !