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.

How to Validate Email Address in SQL Server? - Interview Question of the Week #147 emailvalid-800x233

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)

, ,
Previous Post
What is WorkTable in SQL Server? – Interview Question of the Week #146
Next Post
How to Assign Multiple Variables in a Single T-SQL Query? – Interview Question of the Week #148

Related Posts

13 Comments. Leave new

  • Good one Pinal ?

    Reply
  • Just little note: You missed % and + in your validation.

    Reply
  • nice

    Reply
  • Its not validate with ” econtro;weifwoiaNOIEN3″ string ;)

    Reply
    • Can you please explain more?

      Reply
      • It doesn’t work for “@fir-st@validemail.com”

        with t as (
        SELECT ‘@fir-st@validemail.com’ as EmailAddress
        )
        select * from t
        WHERE EmailAddress LIKE ‘%_@__%.__%’
        AND PATINDEX(‘%[^a-z,0-9,@,.,_,\-]%’, EmailAddress) = 0

  • Tu be able to validate e-mails coming from the domain @x.com, I needed to change it a bit :
    @email LIKE ‘%_@_%.__%’
    AND PATINDEX(‘%[^a-z,0-9,@,.,_,-]%’, @email

    Reply
  • Anudip Kumar Ray
    August 9, 2019 4:30 pm

    Good but not working for the email having space eg : ank @gmail. com

    Reply
  • Shreyasi Brahmbhatt
    November 12, 2019 4:52 am

    How can we validate certain characters allowed in the user name portion but not in the domain name portion of the Email address?
    E.g. now allowing ‘_’ in the domain portion but allowing it in the User name portion of an email address?

    Reply
  • its not working for below email id value :
    SELECT ‘@fir@stv@al.comid.email.com’

    Reply
  • I think comma in PATHINDEX is a mistake.
    It should be
    SELECT EmailAddress AS ValidEmail
    FROM Contacts
    WHERE EmailAddress LIKE ‘%_@__%.__%’
    AND PATINDEX(‘%[^a-z0-9@._\-]%’, EmailAddress) = 0

    Reply
  • I was unable to use this as intended because the fields I draw email address from often have more than one and have them split up by characters like spaces, semicolons, commons, and other words. My solution ended up being to build a scalar function that pulled the actual email addresses out of the columns so i could drop them into a table variable for working with. Now, once they are outside of that column, your solution is okay, but LIKE ignores some things (like whitespace) for the validation your solution employs.

    Reply

Leave a Reply

Menu