Validate Email address in SQL Server

In previous article, I have mentioned how to Best SQL tools for SQL Server and Uniqueidentifier in SQL Server now in this article, I have provided query examples, by executing them you can validate email address in SQL Server.

I will be using below students table for running sample query and check output, so here is the Students table

Simple SQL to Validate Email

So, once I execute the below query to if Email is valid, then I will get invalid email address rows

SELECT * FROM [Students].[dbo].[Student_details] WHERE EmailId NOT LIKE '%_@__%.__%'

This is one of the simplest query to check email validaiton, and it returns rows which doesn't have valid email id.

So here is the output which I get, after executing above query

as you can see above email "wrongemail.com" isn't a valid email id, so this row is returned by query.

Above query matches everything with an @ in the middle, preceded by at least one character, followed by at least two, a dot and at least two for the TLD.

You can write more LIKE patterns that do more specific things, but it is difficult to match all email patterns, there is high possibility that you may skip some or more rules.

We will see another example which will have more complex "Like" pattern, let's take a look at it.

Complex SQL Query to Validate Email

Let's take a look at another SQL Query which will have more validations than the above one.

IF (
     CHARINDEX(' ',LTRIM(RTRIM(@email_address))) = 0 
AND  LEFT(LTRIM(@email_address),1) <> '@' 
AND  RIGHT(RTRIM(@email_address),1) <> '.' 
AND  CHARINDEX('.',@email_address ,CHARINDEX('@',@email_address)) - CHARINDEX('@',@email_address ) > 1 
AND  LEN(LTRIM(RTRIM(@email_address ))) - LEN(REPLACE(LTRIM(RTRIM(@email_address)),'@','')) = 1 
AND  CHARINDEX('.',REVERSE(LTRIM(RTRIM(@email_address)))) >= 3 
AND  (CHARINDEX('.@',@email_address ) = 0 AND CHARINDEX('..',@email_address ) = 0)
)
   print 'valid email address'
ELSE
   print 'not valid'

The above query validate these conditions for an email:

• No embedded spaces
• '@' can't be the first character of an email address
• '.' can't be the last character of an email address
• There must be a '.' somewhere after '@'
• the '@' sign is allowed
• Domain name should end with at least 2 character extension
• can't have patterns like '.@' and '..'

So, email like "email@test..com" is invalid but "email@t.com" is also valid here.

You can execute the query in SQL and get output

Using Query like above, you can add more conditions inside "if", but as I have mentioned above, it is possible that you may skip one or more patterns.

Validate Email in SQL Using PATINDEX

You can also validate email using PATINDEX, which eliminates all e-mail addresses containing characters that are not in the allowed a-z, 0-9, '@', '.', '_' & '-' set of characters.

Here is the sample Query

SELECT EmailId
FROM Students
WHERE EmailId LIKE '%_@__%.__%' 
    AND PATINDEX('%[^a-z,0-9,@,.,_]%', REPLACE(email, '-', 'a')) = 0

Email Address Validation Function

You can also create a function using above query combinations, so it can be used more easily.

CREATE FUNCTION fnIsValidEmail
(
    @email varchar(100)
)
RETURNS bit
AS
BEGIN

    DECLARE @IsValidEmail bit = 0

    IF (@email not like '%[^a-z,0-9,@,.,!,#,$,%%,&,'',*,+,--,/,=,?,^,_,`,{,|,},~]%' --First Carat ^ means Not these characters in the LIKE clause. The list is the valid email characters.
        AND @email like '%_@_%_.[a-z,0-9][a-z]%'
        AND @email NOT like '%@%@%'  
        AND @email NOT like '%..%'
        AND @email NOT like '.%'
        AND @email NOT like '%.'
        AND CHARINDEX('@', @email) <= 65
        )
    BEGIN
        SET @IsValidEmail = 1
    END

    RETURN @IsValidEmail

END

You can modify conditions insde "if" as per your needs, but I found this one better than any other methods above.

That's it, hopefull it will work for you guys.

You may also like to read:

Import csv into SQL server (with query OR without query using SSMS)

How to Create database in sql server management studio (Create table and Insert data)

Download AdventureWorks Database and Restore in SQL server (Step by Step)

Email Validation using Javascript (With OR Without Regex)