SQL Script to Find Invalid Email Addresses

Sql script to find invalid email addresses

SELECT * FROM people WHERE email NOT LIKE '%_@__%.__%'

Anything more complex will likely return false negatives and run slower.

Validating e-mail addresses in code is virtually impossible.

EDIT: Related questions

• I've answered a similar question some time ago: TSQL Email Validation (without regex)
• T-SQL: checking for email format
• Regexp recognition of email address hard?
• many other Stack Overflow questions

FInd invalid email address

SELECT
    email
FROM
    TABLE
WHERE
    email NOT LIKE '%_@_%._%'
AND email NOT LIKE '%list of invalid char%';

Validate email addresses in Mysql

You can use a pure SELECT to validate Email Addresses:

SELECT * FROM `users` WHERE `email` NOT REGEXP '^[^@]+@[^@]+\.[^@]{2,}$';

And now for your question of tracking multiple tables, you can use comma seperated table names right?

SELECT * FROM `users`, `customers`, `clients`
         WHERE `email` NOT REGEXP "^[a-zA-Z0-9][a-zA-Z0-9.!#$%&'*+-/=?^_`{|}~]*?[a-zA-Z0-9._-]?@[a-zA-Z0-9][a-zA-Z0-9._-]*?[a-zA-Z0-9]?\\.[a-zA-Z]{2,63}$";

How to write a query to ensure email contains @

You can use LIKE with wildcards. See here for the wildcards on DB2.

The underscore character ( _ ) represents any single character.

The percent sign character (%) represents a string of zero or more characters.

SELECT email 
FROM YourTable 
WHERE email NOT LIKE '%_@__%.__%'

This will ignore the following cases (simple version for valid emails):

• emails that have at least one character before the @;
• emails that have at least two characters between @ and .;
• emails that have at least two characters between . and the end.

You can see an example in MySql in sqlfiddle.

To add it as a constraint, you do (as mustaccio said in a comment):

alter table your_table add constraint chk_email check (email like '%_@__%.__%')

TSQL Email Validation (without regex)

Very basic would be:

SELECT
  EmailAddress, 
  CASE WHEN EmailAddress LIKE '%_@_%_.__%' 
            AND EmailAddress NOT LIKE '%[any obviously invalid characters]%' 
  THEN 'Could be' 
  ELSE 'Nope' 
  END Validates
FROM 
  Table

This 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 you will never be able to match everything that could be an e-mail address while not letting slip through things that are not. Even with regular expressions you have a hard time doing it right. Additionally, even matching according to the very letters of the RFC matches address constructs that will not be accepted/used by most emailing systems.

Doing this on the database level is maybe the wrong approach anyway, so a basic sanity check as indicated above may be the best you can get performance-wise, and doing it in an application will provide you with far greater flexibility.

T-SQL: checking for email format

I tested the following query with many different wrong and valid email addresses. It should do the job.

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'

It checks these conditions:

• 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 '..'

How to verify valid email address in PostgreSQL?

You can use the POSIX regexp functionality of PostgreSQL:

Have a query with the following condition (email is your email column) in your where clause:

where email !~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$'

Or whatever regular expression works for you.

Validate email address will be accepted by sp_send_dbmail?

You can try this (there are other ways),

--PARAM:START
DECLARE @EmailList varchar(max);

SET @EmailList = 'Jane Doe <DoeJ@xdomain.com>;info.support@mssqltips.com;.info@mssqltips.com;
info..@mssqltips.com;info@mssqltips.c;info@support@mssqltips.com;info.support@mssql_tips.com;
+info@mssqltips.com;info Support@mssqltips.com;info@mssql tips.com;NULL;22@mssqltips.com;@mssqltips.com';
--PARAM:END

--VALIDATION:START
DECLARE @EmailTable TABLE
(
    Email varchar(max),
    IsValid bit
);

INSERT INTO @EmailTable(Email, IsValid)
SELECT LTRIM(RTRIM(value))
,CASE WHEN LTRIM(RTRIM(value)) = '' THEN 0
        WHEN LTRIM(RTRIM(value)) LIKE '% %' THEN 0
        WHEN LTRIM(RTRIM(value)) LIKE ('%["(),:;<>\]%') THEN 0
        WHEN SUBSTRING(LTRIM(RTRIM(value)),CHARINDEX('@',LTRIM(RTRIM(value))),LEN(LTRIM(RTRIM(value)))) LIKE ('%[!#$%&*+/=?^`_{|]%') THEN 0
        WHEN (LEFT(LTRIM(RTRIM(value)),1) LIKE ('[-_.+]') OR RIGHT(LTRIM(RTRIM(value)),1) LIKE ('[-_.+]')) THEN 0                                                                                    
        WHEN (LTRIM(RTRIM(value)) LIKE '%[%' or LTRIM(RTRIM(value)) LIKE '%]%') THEN 0
        WHEN LTRIM(RTRIM(value)) LIKE '%@%@%' THEN 0
        WHEN LTRIM(RTRIM(value)) NOT LIKE '_%@_%._%' THEN 0
        ELSE 1 
    END
FROM STRING_SPLIT(@EmailList, ';');
--VALIDATION:END

--GET VALID EMAIL
DECLARE @ValidEmailList varchar(max);

SELECT @ValidEmailList = COALESCE(@ValidEmailList + ', ', '') + Email
FROM @EmailTable
WHERE IsValid = 1;

--DO SENDING OF EMAIL USING ValidEmailList

Source: Valid Email Address Check with TSQL

---

Related Topics