Validate email addresses in Mysql
Solution 1
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}$";
Solution 2
For the proper email validation, you can use this regex as bellow:
SELECT
*
FROM
`school`
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}$';
Solution 3
Simple SELECT
statement is sufficient, for example:
SELECT * FROM user 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-Z]{2,4}$'
This query handles the Gmail addresses with +
sign and addresses where the host is a single letter.
Solution 4
You can use a UNION
in the VIEW
but then you have to repeat all the WHERE
statement which gives you redundant code. So you would make a helper VIEW
that makes you a UNION
and then apply the WHERE
clause.
Demo here: SQL Fiddle Demo.
That would apply to your SQL somehow like this (untested);
CREATE VIEW `invalid_emails_helper` AS
select `table_with_email_column`.`email` AS `invalidemail`
from `table_with_email_column`
union
select `table_with_email_column`.`email`
from `second_table_with_email_column`
CREATE VIEW `invalid_emails` AS
select `invalidemail` as `email`
from `invalid_emails_helper` as `table_with_email_column`
where ((locate(_latin1'', ltrim(rtrim(`table_with_email_column`.`email`))) <> 0)
or (left(ltrim(`table_with_email_column`.`email`), 1) = _latin1'@')
or (right(rtrim(`table_with_email_column`.`email`), 1) = _latin1'.')
or ((locate(_latin1'.', `table_with_email_column`.`email`,locate(_latin1'@', `table_with_email_column`.`email`)) - locate(_latin1'@', `table_with_email_column`.`email`)) <= 1)
or ((length(ltrim(rtrim(`table_with_email_column`.`email`))) - length(replace(ltrim(rtrim(`table_with_email_column`.`email`)), _latin1'@', _latin1''))) <> 1)
or (locate(_latin1'.', reverse(ltrim(rtrim(`table_with_email_column`.`email`)))) < 3)
or (locate(_latin1'.@', `table_with_email_column`.`email`) <> 0)
or (locate(_latin1'..', `table_with_email_column`.`email`) <> 0));
And yes, the query to check the e-mail address using a regex
as can easily found everywhere in the internet simplifies it further.
Admin
Updated on February 03, 2022Comments
-
Admin over 2 years
This query creates a mysql view that captures bad email address formats in one table. So if a row is inserted in that has
rtrrg.com
as a email it will be recorded in the view. My question is, how do I make the view track more than one table. A second table.The SQL
CREATE VIEW `invalid_emails` AS select `table_with_email_column`.`email` AS `invalidemail` from `table_with_email_column` where ((locate(_latin1'', ltrim(rtrim(`table_with_email_column`.`email`))) <> 0) or (left(ltrim(`table_with_email_column`.`email`), 1) = _latin1'@') or (right(rtrim(`table_with_email_column`.`email`), 1) = _latin1'.') or ((locate(_latin1'.', `table_with_email_column`.`email`,locate(_latin1'@', `table_with_email_column`.`email`)) - locate(_latin1'@', `table_with_email_column`.`email`)) <= 1) or ((length(ltrim(rtrim(`table_with_email_column`.`email`))) - length(replace(ltrim(rtrim(`table_with_email_column`.`email`)), _latin1'@', _latin1''))) <> 1) or (locate(_latin1'.', reverse(ltrim(rtrim(`table_with_email_column`.`email`)))) < 3) or (locate(_latin1'.@', `table_with_email_column`.`email`) <> 0) or (locate(_latin1'..', `table_with_email_column`.`email`) <> 0));