af83

Validate email addresses in Mysql with a pure select

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….)

blog comments powered by Disqus