Validate Email in SQL Using PATINDEX You can also validate email using PATINDEX, which eliminates all e-mail addresses containing characters that are not in the allowed a-z, 0-9, '@', '. ', '_' & '-' set of characters.
a varchar(50 ) would be enough for storing email..
LENGTH(email) - (INSTR(email, '@') + 1) - LENGTH(SUBSTRING_INDEX(email,'. ',-1)) will get the length of the domain minus the TLD (.com, . biz etc. part) by using SUBSTRING_INDEX with a negative count which will calculate from right to left.
use the checkdnsrr function. Note: MX records are not required to host a valid working email server. If the MX record does not exist, the @ A record will be used.
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-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,63}$';
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}$';
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With