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.