From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | Steve Atkins <steve(at)blighty(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Select all invalid e-mail addresses |
Date: | 2005-10-21 20:12:10 |
Message-ID: | 20051021201210.GA98037@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, Oct 21, 2005 at 11:49:54AM -0700, Steve Atkins wrote:
> 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 :))
The backslashes should be escaped or the regular expression should
be quoted with dollar quotes (8.0 and later) -- otherwise the string
parser converts "\." to ".", which matches anything. For example,
the above regular expression considers the following address valid:
foo(at)example?com
Even with that correction the regular expression is still wrong,
especially the ^[^(at)]*@ part at the beginning. See this group's
archives and numerous other sources for further discussion on this
topic.
--
Michael Fuhr
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-10-21 20:12:19 | Re: looking for alternative to MySQL's GROUP_CONCAT function |
Previous Message | Guy Rouillier | 2005-10-21 19:51:50 | Handling of pad characters (was RE: Oracle buys Innobase ) |