Struggling with input validation/check constraint

Question

I'm trying to create a table with an email address column and want to make sure that only addresses in the correct format (contains "@") are allowed. I know how to use the LIKE operator in queries but not how to put a value constraint on a column.

Possible duplicate of [TSQL Email Validation (without regex)](https://stackoverflow.com/questions/229824/tsql-email-validation-without-regex) — Razvan Dumitru, May 27 '19 at 14:33
And you can also do it using regex: https://stackoverflow.com/questions/8928378/using-regex-in-sql-server — Razvan Dumitru, May 27 '19 at 14:34

score 0 · Accepted Answer · answered May 27 '19 at 15:03

You can add the check constraint like this For your basic example:

alter table t add contraint chk_email
    (check (email like '%@%') );

Of course, that is really only a betting. Perhaps something more like this:

alter table t add contraint chk_email
    (check (email like '%_@[^.]%' and               -- one @ and something before and after
            email not like '%@%@%' and         -- not more than one @
            email not like '%[^-.a-zA-Z0-9_]%'  -- valid characters)
    );

This still will allow invalid emails, but it is at least closer.

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

This would be better to do in a programming language that supports regular expressions or already has a built in isValidEmail method.

Validating an email address using a regular expression or a simple like pattern is pretty darn hard to get right, if not nearly impossible.

You can read more about it on I Knew How To Validate An Email Address Until I Read The RFC - And to quote the part I think illustrates the problem best:

These are all valid email addresses!

Abc@def@example.com

Fred\ Bloggs@example.com

Joe.\Blow@example.com

"Abc@def"@example.com

"Fred Bloggs"@example.com

customer/department=shipping@example.com

$A12345@example.com

!def!xyz%abc@example.com

_somename@example.com

Attempting to get all the logic needed to validate such a wide range of possibilities in a T-SQL statement is like attempting to climb the Everest blind-folded with your hands tied behind your back.

You could have a simple validation that might return a lot of false-positives or false-negatives using a simple like pattern like the one suggested in How to Validate Email Address in SQL Server? by Pinal Dave: '%_@__%.__%', but that's really just a naive attempt to clog a dam with a band-aid.

Having said all that, you might be able to use a CLR Scalar-Valued Function to validate your email addresses, using code like the one from this SO post.

Personally, I have no experience with CLR functions, so it would probably be irresponsible of me to try and write you a code example (especially since I don't really have a test environment to check it before I post this answer), but I hope what I've written so far was helpful enough, and with the help of the links in this answer and some web searches you will be able to solve the problem.

Struggling with input validation/check constraint

2 Answers2