Home Email address columns in SQL Server
Reply: 2

Email address columns in SQL Server

Jonathan Allen
1#
Jonathan Allen Published in 2017-11-13 19:26:10Z

What's the correct way to add a check constraint for columns that should only contain email addresses?

We've had buggy software in the past that's added garbage to email columns and I would like to prevent that from happening again.


Please note that "perfect" solutions are not expected. While full RFC compliance would be great, I think most of us can agree just catching obvious mistakes such as "domain/username" and "2017-12-3" would be enough.

And of course it cannot reject any valid email address.

k9100
2#
k9100 Reply to 2017-11-13 19:39:14Z

Try to create a before trigger which checks(use regular expression) if it is a valid email and if it is, then allow it to be added to the column.

Md. Suman Kabir
3#
Md. Suman Kabir Reply to 2017-11-13 20:19:03Z

You can use this :

alter table your_table add constraint chk_email 
CHECK
(
     email like '%_@__%.__%' 
AND (CHARINDEX('.@', email) = 0 AND CHARINDEX('..', email) = 0)  -- prevent patterns like '.@' and '..'
AND LEN(LTRIM(RTRIM(email))) - LEN(REPLACE(LTRIM(RTRIM([email])),'@','')) = 1 -- Single '@' is allowed
)
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.339038 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO