How to validate email addresses in an SQL database

Check if email addresses are really correct in sql without sending any messages

If you have a table full of email addresses that are potentially invalid and you want to clean them up. Maybe the data was collected without input validation or they were collected a long time ago and some email accounts maybe have been closed.

In this example we will assume the table is defined like this.

create table people (id serial primary key, email text);

You could test the emails with a like query or a regex but its not recommended as it can give incorrect answers.

-- using a like query is not recommended
SELECT email, email LIKE '%_@_%.__%' AS isVaild FROM people;

This approach will tell which email addresses look right, but they could still be wrong. See what is email validation.

Validation using Real Email

To validate this table using Real Email you will need to export the table as a CSV file, validate it with Real Email, then import the results.

To export as a CSV file

Export the table as a csv file with headers using the Postgresql copy command. Make sure you include the id primary key, so you can import the results. In MySQL you would use a select into outfile command.

COPY people TO '/tmp/people.csv' WITH (FORMAT CSV, HEADER);

Validate with Real Email

Login to Real Email and upload your CSV file. Depending on the size this may take a little while. For more details see Bulk Email Address validation with CSV File.

Importing results

Import the results into a new temporary table with a status column.

CREATE TABLE people_import (id SERIAL, email text, status text);
COPY people_import FROM '/tmp/people-validated.csv' WITH (FORMAT CSV, HEADER);

If you wanted to create an isValid column to set the status.

ALTER TABLE people add COLUMN isValid boolean;
UPDATE people AS p
SET isValid = (i.status = 'valid' OR i.status = 'unknown')
FROM people_import AS i
WHERE p.id = i.id

Keep in mind the status can be valid, invalid or unknown. Depending on your use case you may want to handle unknown differently. For more details see the Real Email API reference.

Or if you wanted to delete the invalid ones you could do it like.

DELETE FROM people p
USING people_import i
WHERE p.id = i.id AND i.status = 'invalid';

Note. This example is using PostgreSQL syntax for MySQL or SQL Server the syntax might be slightly different.