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?
views:
215answers:
3
+5
A:
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)
Mitch Wheat
2010-02-09 14:48:40
What's a CLR Stored Procedure? Is that the same as a stored procedure? The acronym CLR has me puzzled.
cf_PhillipSenn
2010-02-09 15:44:38
CLR is Common Language Runtime -- a CLR Stored Procedure (or function, etc.) allows you to write a procedure in C# but call it directly from T-SQL.
onupdatecascade
2010-02-10 21:42:30
+1
A:
You can write managed SP using Regex class. email validation according to RFC is complex thing. We simply query AD for user existence.
Sergey Mirvoda
2010-02-09 14:49:37
I agree. The original question was how to validate an email address, not validate that a user is in Active Directory.
cf_PhillipSenn
2010-02-09 15:43:56
+1
A:
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.
AdaTheDev
2010-02-09 14:50:18
I want to put the validation as close to the database level as possible. A rule that isn't enforced at the database level is only a suggestion.
cf_PhillipSenn
2010-02-09 15:45:40
I hear what you're saying, and I'm not saying I disagree completely...but, it should at least *also* be done earlier (i.e. the sooner you can validate the input and catch bad data, the sooner the user can be notified == better user experience). This is a classic example of where you could use SQL CLR (Common Language Runtime) to achieve this at the database level (.NET code running within SQL Server)
AdaTheDev
2010-02-09 16:17:49