Search Postgresql Archives

Re: Select all invalid e-mail addresses

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 !~*
 '^[^@]*@(?:[^@]*\.)?[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


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux