7

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

4
  • What kind of constraint do you want to place? Commented Dec 7, 2015 at 11:25
  • A constraint that stops bad or incomplete email addresses from landing into a database
    – UpwardD
    Commented Dec 7, 2015 at 11:27
  • 2
    There is no easy way to validate an email address (check a related question at SO: stackoverflow.com/questions/201323/…), let alone do this in SQL. Commented Dec 7, 2015 at 11:31
  • you also can use after trigger which checks data from inserted table and see if the name is in valid format.one thing to note is if you are doing bulk insert ,your trigger should handle that scenario as well. Commented Dec 7, 2015 at 12:41

2 Answers 2

6

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

0

I use the following:

select * from api.ParseEmail('[email protected]');

The TVF returns three values: UserName (string), DomainName (string), and IsValid (bit)

This ensures that the string being passed has a valid "shape" because, as others have pointed out, validating an email is tricky business (mainly because what is considered valid changes depending on context, usage, opinion...). You can then stick whatever you want "on top" of this by wrapping it in another function.

Maybe you require that DomainName is longer than X:

select a.UserName
     , a.DomainName
     , case
           when a.IsValid and Len(a.DomainName) > 2 then 1
           else 0
       end as IsValid
from api.ParseEmail as a;

Code:

create function api.ParseEmail (   
    @x nvarchar(256) = null
)
returns table
with schemabinding
as   
return (
    select UserName.s as UserName
         , DomainName.s as DomainName
         , case
               when ([IndexOf@].n != 0 and UserName.s is not null and DomainName.s is not null) then 1
               else 0
           end as IsValid
    from (values(PatIndex('%@%', @x))) as [IndexOf@] (n)
    cross apply (values(NullIf(Substring(@x, 0, [IndexOf@].n), ''))) as UserName (s)
    cross apply (values(NullIf(Substring(@x, [IndexOf@].n + 1, Len(@x) - [IndexOf@].n), ''))) as DomainName (s)
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.