Re: Select all invalid e-mail addresses

On Thu, Oct 20, 2005 at 09:54:13PM +0300, Andrus wrote:
> "Andrew Sullivan" <ajs(at)crankycanuck(dot)ca> wrote in message
>
> > I suggest that if you want to validate TLDs, you pull them off when
> > you write the data in your database, and use a lookup table to make
> > sure they're valid (you can keep the table up to date regularly by
> > checking the official IANA registry for them). At least that way you
> > don't have to change a regex every time ICANN decides to add another
> > TLD. (The regex is wrong anyway, I think: it doesn't have .mobi,
> > which has been announced although isn't taking registrations yet, and
> > it doesn't appear to have arpa, either.)
>
> Andrew, thank you.
>
> I understand now that I do'nt want to validate TLDs at all.
>
> I have an existing database of e-mail addresses. Those addesses are copied
> from letters so they contain < > chars, points, commas etc. stupid
> characters.
> Sometimes two email addresses are copied to this field (contains two @
> sings, spaces or commas). Sometimes web addresses starting with www. and
> without @ are present in email column.
> I want simply to allow user to view those addresses and make manual
> corrections before starting large mailing session in night.
>
> How to write a WHERE clause which selects e-mail addresses which
> are surely wrong ?

... WHERE email !~ '...insert previously mentioned regex here...';

Cheers,
Steve