Re: Select all invalid e-mail addresses

On Wed, Oct 19, 2005 at 09:12:16PM +0300, Andrus wrote:
> I want to select the email addresses which are not valid:
>
> do not contain exactly one @ character,
> contain ; > < " ' , characters or spaces etc.

The rules that define a valid email address are more complex than
most people realize, and even if an address is syntactically valid
that doesn't mean it's valid in the sense that you can deliver mail
to it. Whatever method you end up using, be sure to understand its
limitations.

One possibility would be to write a plperlu function that uses the
Email::Valid module. Here's a trivial example; see the Email::Valid
documentation to learn about its full capabilities:

CREATE FUNCTION is_valid_email(text) RETURNS boolean AS $$
use Email::Valid;
return Email::Valid->address($_[0]) ? "true" : "false";
$$ LANGUAGE plperlu IMMUTABLE STRICT;

You could then do something like:

SELECT * FROM foo WHERE NOT is_valid_email(email_address);

Again, be aware that passing this or any other test doesn't necessarily
mean that an address is truly valid -- it's just an attempt to identify
addresses that are obviously bogus.

--
Michael Fuhr