Microsoft SQL Server email validation

Using Microsoft SQL Server 2005 and above, what code do I use to validate that an email address is correct?

Is there an email data type?

Is there an email check constraint?

Is th开发者_如何学Pythonere an email rule?

Is there an email trigger?

Is there an email validation stored procedure?

I don't usually recommended using a CLR Stored Procedure, but this is a good use of one. SQL's string handling capabilities are not great, whereas using .NET Regex in a CLR Stored Procedure is straightforward and you can use one of the many existing Regex patterns to meet your needs (such as one of these). See Regular Expressions Make Pattern Matching And Data Extraction Easier

Failing that (some DBA's are very strict about enabling the CLR feature), perhaps this might be of interest:

Working with email addresses in SQL Server

Update: in response to question in comments: A CLR stored procedure is a database object inside an instance of SQL Server that is programmed in an assembly created in the Microsoft .NET Framework common language runtime (CLR), such as Visual Basic or C#.

Creating a CLR stored procedure in SQL Server involves the following steps:

• Define the stored procedure as a static method of a class in a language supported by the .NET Framework. For more information about how to program CLR stored procedures, see CLR Stored Procedures. Then, compile the class to build an assembly in the .NET Framework by using the appropriate language compiler.

• Register the assembly in SQL Server by using the CREATE ASSEMBLY statement. For more information about how to work with assemblies in SQL Server, see Assemblies.

• Create the stored procedure that references the registered assembly by using the CREATE PROCEDURE statement. Ref.

See Writing CLR Stored Procedures in C# - Introduction to C# (Part 1)

You can write managed SP using Regex class. email validation according to RFC is complex thing. We simply query AD for user existence.

There isn't a built in mechanism in SQL Server for validating email addresses.

There are numerous regular expressions around that validate an email address (some are much longer than others) e.g. here, see in particular "The Official Standard: RFC 2822" reg ex.

Now, SQL Server doesn't have built in support to run regular expressions so if you truly wanted to do it within SQL, you'd need to use the CLR functionality - i.e. write a .NET function that performs the validation, which can then be called from SQL.

However, I'd be validating the email address earlier, before it comes in to the database.

If OLE automation is enabled, you can easily create a UDF to handle regexes, and then just call that:

CASE WHEN dbo.RegExFind
    (
    'joe@stackoverflow.com',
    '^[a-z0-9][a-z0-9._-]*@[a-z0-9][a-z0-9.-]*[a-z0-9]\.[a-z][a-z]+$',
    1  -- Case-insensitive or not
    ) = 1 THEN 'OK' ELSE 'Not OK' END

I don't recall where I got the code for my RegExFind() UDF, but it looks like the code is already present on StackOverflow, here.

As others have mentioned you can use a regex to validate the emails but with limited accuracy.

-- you could, but not recommended
SELECT email, email LIKE '%_@_%.__%' AS isVaild FROM people;

You may find it better to use a service like Real Email to validate the emails, which not only checks if the email looks correct, but if the domain and account are valid.

You could export your data as a csv then validate it. For more see How to validate emails in an sql database.