7

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

  • What kind of constraint do you want to place? – ypercubeᵀᴹ Dec 7 '15 at 11:25
  • A constraint that stops bad or incomplete email addresses from landing into a database – UpwardD Dec 7 '15 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. – ypercubeᵀᴹ Dec 7 '15 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. – TheGameiswar Dec 7 '15 at 12:41
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

| improve this answer | |
0

I use the following:

select * from api.ParseEmail('mail@domain.com');

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)
);
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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