My javascript validated the e-mail addresses, my PHP code validated them why not let mysql have a go at it too?
Here is a view for mysql (don't even think about trying to use this with MySQL < 5.0.1) that will validate email addresses with a select statement, no need for UDF or anything fancy
this is based on code by Narayana Vyas Kondreddi http://vyaskn.tripod.com/handling_email_addresses_in_sql_server.htm
-
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));
So you can do stuff like delete all lines from the table that have an invalid email:
-
DELETE FROM table_with_email_column WHERE `table_with_email_column`.`email` IN (SELECT
-
invalidemail FROM invalid_emails);
or just select all the valid lines :
-
SELECT FROM table_with_email_column WHERE `table_with_email_column`.`email` NOT IN (SELECT
-
invalidemail FROM invalid_emails);
Sadly, this view is not updateble ...
Probably this is not at all precise... so I would really like to find a nice test dataset for emails that I can use to validate the validation against the RFC (with 4 letter TLDs and the works....)
Entries (RSS)