Last week I mentioned a little about domain rules but other than using a few simple ones, I did not mention a lot of detail. Well, just for the record, the following list gives you an idea of all of the different Boolean expression types I can build with a domain rule:
- Length is equal to
- Length is greater than or equal to
- Length is less than or equal to
- Value is equal to
- Value is not equal to
- Value contains
- Values does not contain
- Value begins with
- Value ends with
- Value is numeric
- Value is date\time
- Value is in
- Value is not in
- Value matches pattern
- Values does not match pattern
- Value contains pattern
- Value does not contain pattern
- Value matches regular expression
- Value does not match a regular expression
Most of these are (or should be) self-explanatory. However, this week I want to take a quick look at the pattern matching expressions.
First, these are not the same as building a regular expression. I will cover them in the future. Rather a pattern is much simpler. In fact, there are only four basic rules to building a pattern. They are:
- You can define a position in the string to represent a letter
- You can define a position in the string to represent a number
- You can hard-code a position in the string to be a specific special character such as a hyphen
- You can define a position in the string to be an optional letter or number
For example, one of the patterns I might want to match is for a zip code in the United States:
(Note: I can use any numeric digit 0-9 to represent a number in any position of the string. Therefore the pattern: 00000 is the same as 12345 is the same as 99999. They all merely represent a string of five digits.)
This pattern implies that the user must enter a string of 5 digits, not 4 and not 6. Five digits and only five digits will be accepted.
This means that the following zip code would not be accepted using this method:
The first example is only four digits and is short by one digit. The second example has 6 digits and has one too many digits. The remaining two examples include letters and numbers which while valid in some countries is not valid in the United States. To allow these I would need a patter like:
However, some people may want to enter their zip plus 4 zip code. To do this, I need to allow for the hyphen between the fifth and the sixth digit. Therefore, I might use something like:
That pattern would be great if everyone entered their full zip plus 4 zip code. However, not everyone knows what their full zip code is and while using the full zip code makes the sorting and delivery of mail more efficient, it is not yet required. Unfortunately, if I specified this pattern for my zip code field domain rule, records with only the standard 5-digit zip code values would fail the rule.
In order to give the user an option as to whether to use either their 5-digit zip code or their 9-digit zip code I could do this:
Notice that the two separate clauses for 5-digit zip code and 9-digit zip codes are joined with an ‘or’ connector. It should be obvious that an ‘and’ connector would not be appropriate in this case. Anyway, there is a simpler way to accomplish the same rule using a single clause by defining the last 5 characters (the hyphen and the 4 digits) as optional as shown in the following figure.
As seen in this example, I can combine multiple character positions within parenthesis to indicate that they are optional, that includes special characters.
While all of the above examples defined patterns for the entire string to match, I can also define patterns that the domain values must not match for the values to be valid. Similarly, I can use pattern matching to match a portion of the string by using the value contains pattern option. Note that values that begin or end with the matching pattern would be valid. The pattern could even be in the middle of the string. I can even evaluate strings to be valid if they do not contain a specific pattern anywhere within the string.
However simple pattern matching may be, it has its limitations. The biggest limitation is that I cannot specify a specific character in a specific position. For example, I cannot specify a 4-character string in which the first letter can only be ‘A’, ‘B’, ‘C’, or ‘D’. This type of comparison and many more require the greater complexity of the regular expression clause which I will look at next time.
Until then, c’ya!