Check Constraint enforcing a valid email address

[Luzian]

I found a nice article that takes advantage of SQL Server 2005's CLR integration @

http://davidhayden.com/blog/dave/archive/2006/04/18/2917.aspx

The code is pretty simple, although I've never gotten around to understanding regular expressions.

public partial class UserDefinedFunctions
{
    static readonly string emailRegex =
        @"^([\w-\.]+)@((\[[0-9]{1,3}\.[0-9]"
        + @"{1,3}\.[0-9]{1,3}\.)|(([\w-]+\.)+))"
        + @"([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$";

    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean
       ValidateEmailAddress(SqlString emailAddress)
    {
        Regex regex = new Regex(emailRegex);
        return regex.IsMatch(emailAddress.Value);
    }
}

After deploying your SQL Server project (in visual studio 2005), slap your constraint on an email address column of some table

ALTER TABLE [Users]
ADD CHECK ([dbo].[ValidateEmailAddress]([email]) = (1))