Database Reference
In-Depth Information
/* Use of the backslash for escape clauses */
/* \\w denotes the characters 0-9, a-z, A-Z, or the
underscore(_) */
SELECT '123a567' ˜ '\\w'
/* returns True */
SELECT '123a567+' ˜ '\\w'
/* returns True */
SELECT '++++++++' ˜ '\\w'
/* returns False */
SELECT '_' ˜ '\\w'
/* returns True */
SELECT '+' ˜ '\\w'
/* returns False */
Regular expressions can be developed to identify mailing addresses, e-mail
addresses, phone numbers, or currency amounts.
/* use of more complex regular expressions */
SELECT '$50K+' ˜ '\\$[0-9]*K\\+'
/* returns True */
SELECT '$50K+' ˜ '\\$[0-9]K\\+'
/* returns False */
SELECT '$50M+' ˜ '\\$[0-9]*K\\+'
/* returns False */
SELECT '$50M+' ˜ '\\$[0-9]*(K|M)\\+'
/* returns True */
/* check for ZIP code of form #####-#### */
SELECT '02038-2531' ˜ '[0-9]{5}-[0-9]{4}'
/* returns True */
SELECT '02038-253' ˜ '[0-9]{5}-[0-9]{4}'
/* returns False */
SELECT '02038' ˜ '[0-9]{5}-[0-9]{4}'
/* returns False */
So far, the application of regular expressions has been illustrated by including the
Boolean comparison in a
SELECT
statement as if the result of the comparison was
to be returned as a column. In practice, these comparisons are used in a
SELECT
statement's
WHERE
clause against a table column to identify specific records of
interest. For example, the following SQL query identifies those ZIP codes in a table
of customer addresses that do not match the form #####-####. Once the invalid
ZIP codes are identified, corrections can be made by manual or automated means.
SELECT address_id,
customer_id,
city,
state,
zip,
country
FROM customer_addresses
WHERE zip !˜ '^[0-9]{5}-[0-9]{4}$'
address_id customer_id city state zip country
