Re: Select all invalid e-mail addresses

> Suggestions: use text or varchar for the email address, don't embed
> newlines in the regular expression, and if you use dollar quotes
> and the regular expression ends with a dollar sign then quote with
> a character sequence other than $$.

Michael,

thank you. Excellent!

I'm afraid that using VARCHAR instead on CHAR may cause some parts of my
application to stop working. So I used TRIM function.

This regex allows email addresses containing two dots without any letters,
like eeta(dot)(dot)soft(at)online(dot)ee
I havent seen any email of such kind.

Andrus.

create temp table customer ( email char(60)) on commit drop;
insert into customer values( 'eeta(dot)(dot)soft(at)online(dot)ee');
SELECT email FROM customer WHERE email!='' and email IS NOT NULL and
TRIM(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)$$_$