613-518-1166 info@zimdatabases.com
Select Page

# How To Construct a Validation Rule for Alphanumeric Fields

The available pattern symbols for constructing alphanumeric validation rules are

• A stands for any letter (A-Z, a-z)

• X stands for any letter or digit (A-Z, a-z, 0-9)

• 9 stands for any digit (0-9)

• Z stands for any digit or blank (0-9, the space character)

• (all others) exact match. To obtain an exact match for A, X, 9, or Z, add the escape character (e.g., A, X, 9, Z)

The available syntax characters for constructing alphanumeric validation rules are

 [ ] Encloses a set of alternative characters. (Note: All characters inside brackets are taken literally; for example, A is the letter “A”, not the pattern symbol A.) – Used inside brackets to denote a range of characters. | Separates alternative validation patterns

A telephone number can consist of a three-digit area code enclosed in parentheses, as well as a seven-digit number containing a hyphen, or just the seven-digit number and hyphen. The first digit of the area code must fall in the range 2 through 9; the second digit must be 0 or 1; and the third digit is unrestricted. The first digit of the prefix must fall in the range 2 through 9. The remaining numbers are unrestricted. So the rule can be expressed as follows:

([2-9][0-1]9)[2-9]99-9999|[2-9]99-9999

Input values that are accepted as legal include 212-643-9763 and 643-9763; input values that are rejected include 222-643-9763 and 143-9763.

Examples of Alphanumeric Validation Rules

The following table contains examples of alphanumeric validation rules and the values that might be legal or illegal under those rules:

 Validation Rule Legal Value Illegal Value AB qB q” XX-XXXX AL-9632 \$63.09 Q[L-P]A QLA or QMA or QPA QRA or qLa or QPB [ACEG] A or C or E B or D or F [Ax-zB] A or y or B a or Y or b A|9 m or 3 or Q ? or \$ or % Y|N Y or N A or 5