Celko's Email-Address-Validation SQL Puzzle

You've probably noticed that many Web sites use the customer's email addresses as an identifier - an email address has a very easy validation. We have all received messages something like this: "Thanks for signing up for 'The Leech Farmer's Monthly' email newsletter! We are sending you a confirmation at your email address with your temporary password."

How many ways can you write CHECK() column constraint to validate an email address? Call the column "email" just so all entries look alike (yes, you really should use "_email" to follow ISO-11179 rules). You are not allowed to do an external function call; it has to be in Standard SQL or a dialect extension.Standard SQL has a regular expression called the SIMILAR TO predicate which is based on the POSIX standards. Likewise, SQL Server has extensions to the LIKE predicate; Oracle has a function call, and so forth for other vendors. These are the obvious (and probably the fastest) answers in each dialect. Hint #1: Google up a regular expression site and look at grep() family versions for solutions. Hint #2: You know that you have to have a single @ in the string, but did you remember that the postfix can be a country ISO code, or a three-letter domain? Hint #3: Do you know where the letters, digits and special symbols can and cannot be found in the email string? Do you know what special symbols are allowed? I'll post my answer to this puzzle next week. In the meantime, post your entry in the comment field below and I'll send the first respondent offering three valid approaches a free copy of one my books on SQL. Plus, don't miss this month's "Celko on SQL" column, "Natural, Artificial, Exposed and Surrogate Keys Explained." Joe Celko is an independent consultant in Austin, Texas, and the author of SQL Puzzles and Answers (2006), Joe Celko's SQL for Smarties: Advanced SQL Programming (2005), and Joe Celko's Trees and Hierarchies in SQL for Smarties (2004).You've probably noticed that many Web sites use the customer's email addresses as an identifier. We have all received messages something like this: "Thanks for signing up for 'The Leech Farmer's Monthly' email newsletter! We are sending you a confirmation at your email address with your temporary password." How many ways can you write CHECK() column constraint to validate an email address?