Find invalid emails

How to select invalid email addresses from a column. How to reject attempts to insert invalid emails into a column.

Selecting invalid emails

A valid email has at least one character, then a @ character, then at least one character, then a dot, then at least another character.

Here’s how to select emails that don’t follow these rules:

SELECT id, email FROM users WHERE
    email NOT LIKE '_%@_%._%'
;

Rejecting invalid emails from a column

To make it impossible to insert invalid email into a column:

CREATE TABLE users (
    id INTEGER,
    email VARCHAR(50) CHECK (email LIKE '_%@_%._%'),
    PRIMARY KEY (id)
);

However, this check may still miss some invalid emails: for example texts that have multiple @ characters, or end with a dot.