Re: Select all invalid e-mail addresses - Mailing list pgsql-general

From Michael Fuhr
Subject Re: Select all invalid e-mail addresses
Date
Msg-id 20051019191849.GA74963@winnie.fuhr.org
Whole thread Raw
In response to Select all invalid e-mail addresses  ("Andrus" <eetasoft@online.ee>)
Responses Re: Select all invalid e-mail addresses  (Hannes Dorbath <light@theendofthetunnel.de>)
List pgsql-general
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

pgsql-general by date:

Previous
From: "Guy Rouillier"
Date:
Subject: Re: Select all invalid e-mail addresses
Next
From: "Dann Corbit"
Date:
Subject: Re: [pgsql-advocacy] Oracle buys Innobase