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 'email@example.com' UNION ALL SELECT 'first@validemail' UNION ALL SELECT '@validemail.com' UNION ALL SELECT 'firstname.lastname@example.org' 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)