Celko's Email-Address-Validation SQL Puzzle - InformationWeek

InformationWeek is part of the Informa Tech Division of Informa PLC

This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them.Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.

IoT
IoT
Software // Information Management
Commentary
9/17/2007
07:26 AM
Joe Celko
Joe Celko
Commentary
50%
50%

Celko's Email-Address-Validation SQL Puzzle

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?

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?

We welcome your comments on this topic on our social media channels, or [contact us directly] with questions about the site.
Comment  | 
Print  | 
More Insights
Slideshows
IT Leadership: 10 Ways to Unleash Enterprise Innovation
Lisa Morgan, Freelance Writer,  6/8/2021
Commentary
Preparing for the Upcoming Quantum Computing Revolution
John Edwards, Technology Journalist & Author,  6/3/2021
News
How SolarWinds Changed Cybersecurity Leadership's Priorities
Jessica Davis, Senior Editor, Enterprise Apps,  5/26/2021
White Papers
Register for InformationWeek Newsletters
Video
Current Issue
Planning Your Digital Transformation Roadmap
Download this report to learn about the latest technologies and best practices or ensuring a successful transition from outdated business transformation tactics.
Slideshows
Flash Poll