Email Validation

Question

I have some email addresses in a SQL table.
How can I validate if the addresses are real?
I don’t mean the syntax check. I want to check if they are found in real world.

Answer 1

Hi @Hema Ramachandran ,

There isn't a built in mechanism in SQL Server for validating email addresses.
you can use a regex to validate the emails but with limited accuracy, like:

SELECT EmailAddress AS ValidEmail  
FROM Contacts  
WHERE EmailAddress LIKE '%_@__%.__%'  
        AND PATINDEX('%[^a-z,0-9,@,.,_,\-]%', EmailAddress) = 0  
GO  

How to Validate Email Address in SQL Server?
Microsoft SQL Server email validation

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Note! Sending emails without the pre-acceptance of the owner of the email is illegal in many countries (it is a crime) !

The only way to validate email address is against the email server which host the email. If the DNS server point the emails to specific server and the email server does not provide services to that email or there is no email server that respond to the request then you get error. If you do not get error then it does not mean the email is valid! a smart server admin can configure a catch-all respond to prevent validate emails against the server. Therefore, the only way to really validate email address is to get repose from the email - for example sending link in the email and ask the user to click it to validate his email and approve getting emails from you.

This has nothing to do with SQL Server.

You can use the data in the database from any external application which can send the email

I recommend to search google for one of the recording of my lecture about how to manage User Groups and Distribution List using Logic Apps and Azure SQL Database. The basic procedure include a few steps based on Logic apps which include: (1) Registration using Windows form -> on submit the data is stored in the database and an email send to the email used in the form. (2) The user click the link -> logic app triggered and change the status of the row in the database to approved. (3) Sending email to all the DL which their email is approved. (4) Windows form to remove your email from the DL

Answer 3

There is no way to do that programmatically, for many reasons. You would have to send an email with a link "verify this email" and the user would need to click the link.