 |
SQL Puzzlers, by Joe Celko
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). See More by Joe Celko 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 " 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).  E-MAIL | SLASHDOT | DIGG This is a public forum. CMP Technology and its affiliates are not responsible for and do not control what is posted herein. CMP Technology makes no warranties or guarantees concerning any advice dispensed by its staff members or readers. Community standards in this comment area do not permit hate language, excessive profanity, or other patently offensive language. Please be aware that all information posted to this comment area becomes the property of CMP Media LLC and may be edited and republished in print or electronic format as outlined in CMP Technology's Terms of Service. Important Note: This comment area is NOT intended for commercial messages or solicitations of business.
|
|
Blog Channels
Cindi Howson on Business Intelligence The Brain Food Blogger Tony Byrne on Content Management SQL Puzzlers by Joe Celko Rajan Chandras on IT & Information Management Seth Grimes on Analytics In Context by Doug Henschen Phil Kemelor on Web Analytics Sandy Kemsley's Column Two Nelson King on Enterprise App Development SharePoint TrendWatch, by Shawn Shell Enterprise Architecture TrendWatch, by Kas Thomas Natural Insight, By Mark Madsen Alan Pelz-Sharpe on Content Management Mark Smith on Performance Management Neil Raden on Business Intelligence Bruce Silver on Business Process Management Product Maven  Subscribe to RSS Archives
|
| ||||||||||||||||||||||||||||||||
