Enforce Data integrity on an Email column in a database

I want to put a constraint on an email column like so: xxxx@xxxx.yyy
Obviously "x" is of various lengths and x is some piece of string data and .yyy is a domain type of .com, .gov etc

This is way too big a task if you wanted to do it properly. If you are able to use SQL CLR though this is how we do it at our company:

using System;
using System.Net.Mail;
using Microsoft.SqlServer.Server;

namespace Functions
{
    public static class Utilities
    {
        [SqlFunction]
        public static bool IsValidEmail(string email)
        {
            if (string.IsNullOrEmpy(email))
                return false;

            bool isValid = false;
            try
            {
                // use the validation provided by the System.Net.Mail.MailAddress class            
                var mailAddress = new MailAdress(email); 
                isValid = true;
            }
            catch(FormatException)
            {
                isValid = false;
            }
            return isValid;
        }
    }
}

And then assuming you deploy it as dbo.fn_IsValidEmail you can use it in your check constraint like you would any other scalar function like so:

create table dbo.tbl
(
    id int identity not null,
    email nvarchar(256) not null,
    constraint ck_tbl_isValidEmail check (dbo.fn_IsValidEmail (email))
);

If your column allows NULL then you will need to change the first part of the function to this instead:

if (email == null)
    return true;
if (email == string.Empty)
    return false;

Documentation:

System.Net.Mail.MailAddress

Microsoft.SqlServer.Server.SqlFunctionAttribute

CLR Functions

StackExchange Database Administrators Q#123078, answer score: 6