Click on the banner to learn about and purchase my database training on Azure

SQL Server - How to validate email and email domain using SQLCLR (C#)

Views: 1.015 views
Reading Time: 4 minutes


Speak guys!
I imagine that at some point, you must have had to check one or more emails in SQL Server, to see if the email is valid.

You must have already thought about validating the email domain, to know if the domain is valid. I am not referring to knowing if the domain was typed correctly and does not have special characters, for example, but to return if the domain really exists: emai[email protected] is valid, but [email protected] it is not, for example.

We will see now in this post, how it is possible to do this by SQL Server

Important: To learn more about SQLCLR and understand what it is and how it works, read the article Introduction to SQL Common Language Runtime (CLR) in SQL Server or go to CLR Category and see everything I've done using this SQL Server extension.

How to validate email in SQL Server

I had already demonstrated another solution for email validation in the article Validating CPF, CNPJ, Email, Phone, and Zip Code in SQL Server, using only the SQL LIKE function and some regular expressions. But the validation ended up not being 100%, because there were some cases where invalid emails were being classified as valid, such as [email protected].

Domains cannot have hyphens as the first or last letter and I fixed this in this new function.

Source code of the fncValida_Email.cs function
Click here to view the source code

Example of use:

Important: To learn more about regular expressions in SQL Server, read the article SQL Server - How to use regular expressions (RegExp) in your database.

How to validate email domain in SQL Server

Another need that can be quite common is to validate the e-mail domain, that is, to know if [email protected] actually exists or was typed incorrectly.

There are some rules that follow RFC 5321 to define that domains cannot have hyphens as a first or last letter, for example and could be used to know if a domain is following these rules, but that doesn't mean that the domain actually exists.

To solve this problem in a more precise way, I decided to create an SQLCLR function that makes a request to the domain using the nslookup command and returns whether the MX records returned from that domain are valid or not.

Source code of the fncValida_Email_Dominio.cs function
Click here to view the source code

Example of use

Important: As the function fncValida_Email_Dominio needs to access external resources, the PERMISSION_SET property on assembly creation cannot be set to SAFE. I tried to create as EXTERNAL_ACCESS, but due to the use of the Process class, from the System.Diagnostics library, the assembly starts using unmanaged code, and this is only allowed with the UNSAFE permission in the assembly creation.

Due to the UNSAFE permission, if the SQL Server version is below 2017, the TRUSTWORTHY property of the database will be changed to True, so that this Assembly can be used. If the SQL Server version is equal to or above 2017, this is not necessary as the assembly will be added to the list of trusted_assemblies.

To understand the risks of this enabled TRUSTWORTHY property, read the article SQL Server - Understanding the Risks of TRUSTWORTHY Property Enabled in a Database or take my course SQL Server Security.

How to create these objects in my database

Did you like these two functions? So let's implement them in your database and for that, I prepared a very easy script for you.

Click here to view the source code

Just copy this code, paste it into your SQL Server Management Studio (SSMS) and hit F5. And that's all. Now just start using as the examples I showed in this post.

I hope you enjoyed this tip and see you in the next post.