Check if Email Addresses are really correct in an 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 and bounce. See what is email validation.
Validation using Real Email
To validate this table using MailValidation.io you will need to export the table as a CSV file, validate it with our Bulk Upload tool, 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 MailValidation.io
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 email validation 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.
