Re: Select all invalid e-mail addresses

On Fri, Oct 21, 2005 at 08:15:23PM +0300, Andrus wrote:
> >> How to write a WHERE clause which selects e-mail addresses which
> >> are surely wrong ?
> >
> > ... WHERE email !~ '...insert previously mentioned regex here...';
>
> Steve,
>
> thank you.
>
> I tried

[snip]

SELECT email FROM customer
WHERE email !~*
'^[^(at)]*@(?:[^(at)]*\(dot))?[a-z0-9_-]+\.(?:a[defgilmnoqrstuwz]|b[abdefghijmnorstvwyz]|c[acdfghiklmnoruvxyz]|d[ejkmoz]|e[ceghrst]|f[ijkmorx]|g[abdefhilmnpqrstuwy]|h[kmnrtu]|i[delnoqrst]|j[mop]|k[eghimnprwyz]|l[abcikrstuvy]|m[acdghklmnopqrstuvwxyz]|n[acefgilopruz]|om|p[aefghklmnrtwy]|qa|r[eouw]|s[abcdeghijklmnortvyz]|t[cdfghjkmnoprtvwz]|u[agkmsyz]|v[aceginu]|w[fs]|y[etu]|z[amw]|edu|com|net|org|gov|mil|info|biz|coop|museum|aero|name|pro|mobi|arpa)$'

...should be closer. Fixes one typo in the range, uses valid pg format regex, rather
than perl regex and had a couple of pedant-fixes in the TLDs supported.

It's syntactically correct, and appears to do the right thing on my production
DB here (which conincedentally has a customer table with an email field :)), but
you should make sure you understand what the regex actually does.

Cheers,
Steve