| Controlling data quality is
one of the most important tasks on any data migration or integration
project. Most ETL platforms allow you to use regular expressions
to identify improperly formatted input data. In this article,
I will cover some often-used cases for regular expressions
in the world of data quality.
1. Names
A name typically does not contain non-alphabetic characters
such as digits or symbols, unless you are the artist formerly
known as Prince. The following regular expression validates
that a name contains one or more alphabets and spaces.
^[a-zA-Z\ ]+$
Note the use of ^ at the beginning and $ at the end. This
ensures that the match happens from the beginning to the end
of the text, and not anywhere in the middle.
2. Integers
The following regular expression ensures that the input contains
one or more digits and nothing else:
^[0-9]+$
3. Currency
Currency values typically have one or more digits. It can
optionally have a decimal point followed by two digits. For
example, 300, and 400.50 are valid but 500.757 is not. Here
is the regular expression:
^[0-9]+(\.[0-9]{2})?$
4. Credit Card Numbers
Credit card numbers are 16 digits in length. Sometimes, the
numbers are grouped as four digits with either a space or
a dash separating the groups. Here is the regular expression:
^([0-9]{4}(\ |-|)){4}$
5. North American Phone Numbers
Phone numbers in North America are typically stored in the
format (NNN)NNN-NNNN. The following expression can be used
to identify phone numbers that do not follow this format:
^\([0-9]{3}\)[0-9]{3}-[0-9]{4}$
5. Email Addresses
Email addresses typically have the format of username@domain.ext
where username can have alphabets, digits, dashes and dots.
The domain name can have alphabets, dashes and dots. The extension
is either a two-letter (nz, in) word or a three-letter word
(com, edu, gov). Here is the syntax for such a regular expression:
^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$
I hope this was a good introduction into identifying bad
data using regular expression. Of course, the flexibility
that regular expressions provide go far beyond what I have
covered here. If you are interested in learning more, you
can find very good articles on the Internet. |
