Validating and Reformatting Data - MySQL - page 425

To look for URL values that begin with a protocol specifier of http:// , ftp:// , or
mailto: , use an alternation that matches any of them at the beginning of the string.
These values contain slashes, so it's easier to use a different character around the pattern
to avoid escaping the slashes with backslashes:
m #^(http://|ftp://|mailto:)#i
The alternatives in the pattern are grouped within parentheses because otherwise the ^
anchors only the first of them to the beginning of the string. The i modifier follows the
pattern because protocol specifiers in URLs are not case sensitive. The pattern is other‐
wise fairly unrestrictive because it permits anything to follow the protocol specifier. Add
further restrictions as necessary.
12.8. Using Table Metadata to Validate Data
Problem
You must check input values against the legal members of an ENUM or SET column.
Solution
Get the column definition, extract the list of members from it, and check data values
against the list.
Discussion
Some forms of validation involve checking input values against information stored in
a database. This includes values to be stored in an ENUM or SET column, which can be
checked against the valid members stored in the column definition. Database-backed
validation also applies to values that must match those listed in a lookup table to be
considered legal. For example, input records that contain customer IDs can be required
to match a row in a customers table, and state abbreviations in addresses can be verified
against a table that lists each state. This recipe describes ENUM - and SET -based validation,
and Recipe 12.9 discusses how to use lookup tables.
One way to check input values that correspond to the legal values of ENUM or SET columns
is to get the list of legal column values into an array using the information in INFORMA
TION_SCHEMA , then perform an array membership test. For example, the favorite-color
column color from the profile table is an ENUM defined as follows:
mysql> SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS
-> WHERE TABLE_SCHEMA = 'cookbook' AND TABLE_NAME = 'profile'
-> AND COLUMN_NAME = 'color';