Microsoft SQL Server email validation

Question

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 there an email rule?
Is there an email trigger?
Is there an email validation stored procedure?

Answer 1

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)