MySQL Cookbook, 2nd Edition

Using Pattern Matching to Validate Data

Problem

You need to compare a value to a set of values that is difficult to specify literally without writing a really ugly expression.

Solution

Use pattern matching.

Discussion

Pattern matching is a powerful tool for validation because it enables you to test entire classes of values with a single expression. You can also use pattern tests to break up matched values into subparts for further individual testing or in substitution operations to rewrite matched values. For example, you might break a matched date into pieces so that you can verify that the month is in the range from 1 to 12, and the day is within the number of days in the month. You might use a substitution to reorder MM-DD-YY or DD-MM-YY values into YY-MM-DD format.

The next few sections describe how to use patterns to test for several types of values, but first let’s take a quick tour of some general pattern-matching principles. The following discussion focuses on Perl’s regular expression capabilities. Pattern matching in Ruby, PHP, and Python is similar, although you should consult the relevant documentation for any differences. For Java, use the java.util.regex package.

In Perl, the pattern constructor is / pat /:

$it_matched = ($val =~ /pat/);    # pattern match

Put an i after the / pat / constructor to make the pattern match case-insensitive:

$it_matched = ($val =~ /pat/i);   # case-insensitive match

To use a character other than slash, begin the constructor with m. This can be useful ...