How to Validate Email Address in SQL Server? – Interview Question of the Week #147

Question: How to Validate Email Address in SQL Server?

Answer: I just received this question in an email a week ago. The matter of fact, I have answered this email quite a many times before on various forums and on social media. However, when I searched this blog, I could not find a post related to this topic. Hence, I decided to write this email.

Let us create a column called EmailAddress in the table named Contacts. Once we create the table, we will insert few valid and invalid email address in it.

USE tempdb
GO
CREATE TABLE Contacts (EmailAddress VARCHAR(100))
INSERT INTO Contacts (EmailAddress)
SELECT 'first@validemail.com'
UNION ALL
SELECT 'first@validemail'
UNION ALL
SELECT '@validemail.com'
UNION ALL
SELECT 'second@validemail.com'
UNION ALL
SELECT 'firstvalidemail.com'
GO

Next we will run following script which will only select the valid email address:

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

We can also use NOT condition in the WHERE clause and select all the invalid emails as well.

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

I guess, that’s it. Let me know if you are using any other trick to find out valid and invalid email addresses in SQL Server. Please post a comment and I will be happy to post it here with due credit.
Reference: Pinal Dave (https://blog.sqlauthority.com)