Data quality regular expressions
You will find here a few useful REGEX that I have found or written, they can be used easily to validate values within data by using the REGEX() function in Datamartist.
For example, here is a simple expression that validates if a string contains a valid Canadian postal code or not:
^[ABCEGHJKLMNPRSTVXY]{1}\d{1}[A-Z]{1} *\d{1}[A-Z]{1}\d{1}$
If in Datamartist you have a column called “PostalCode” in a data set, you can add a column called “ValidCanPC”, for example, using the following expression:
REGEX([PostalCode],”^[ABCEGHJKLMNPRSTVXY]{1}\d{1}[A-Z]{1} *\d{1}[A-Z]{1}\d{1}$”)
Combining REGEX
To combine more than one regular expression, you can either add the expressions together using the regex “OR” operator which is “|”, or you can use the OR function to combine two REGEX() functions.
For example, to determine if a postal code is either a valid Canadian postal code, OR a valid US postal code, you can use the following:
REGEX([PostalCode],”^\d{5}(-\d{4})?$|^[ABCEGHJKLMNPRSTVXY]{1}\d{1}[A-Z]{1} *\d{1}[A-Z]{1}\d{1}$”)
Where the “|” you see divides the two regex, the first shorter one being that for the US postal codes.
Find below some more regular expressions you might find useful (Please note, while I have tested many of these, I can’t vouch for complete accuracy- “Caveat Emptor” and please let me know if you find any issues (or have an improved version)- never assume any regex is infallible- depending on what they are trying to detect, false positives and false negatives are of course possible.
In a way, far from being a cure all for data validation, regular expressions underline how difficult it is- but they are a useful tool.
Postal Codes
Canada
^[abceghjklmnprstvxyABCEGHJKLMNPRSTVXY][0-9][abceghjklmnprstvwxyzABCEGHJKLMNPRSTVWXYZ][ ]?[0-9][[abceghjklmnprstvwxyzABCEGHJKLMNPRSTVWXYZ][0-9]
US
^\d{5}(-\d{4})?$
United Kingdom
^([A-PR-UWYZa-pr-uwyz0-9][A-HK-Y0-9][AEHMNPRTVXYaehmnprtvxy0-9]?[ABEHMNPRVWXYabehmnprvwxy0-9]? {1,2}[0-9][ABD-HJLN-UW-Zabd-hjln-uw-z]{2}|GIR 0AA)$
Email Address
^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$
Phone Number
This one is a real killer- so many different ways to write phone numbers, and so many different formats- just a few examples;
North American phone numbers (allows an “x” and extension on the end):
^[1]?[ ]?[\\(]{0,1}([0-9]){3}[\\)]{0,1}[ ]?([0-9]){3}[ ]?[-]?[ ]?([0-9]){4}[ ]?([xX][ ]?[0-9]{1,5})?$
UK Phone numbers
(\s*\(?0\d{4}\)?\s*\d{6}\s*)|(\s*\(?0\d{3}\)?\s*\d{3}\s*\d{4}\s*)
Web page URI
^(http\://)?[a-zA-Z0-9\-\.]+\.[a-zA-Z]{2,6}(/\S*)?$
Dates
This one is obviously a huge one, considering how many different formats their are- here is an example of just one format, yyyy-mm-dd. Obviously, while a universal date detector is probably not something you’d want to try to build, as you have specific needs building patterns provides a handy way to validate strings.
^(19|20)\d\d[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])$
