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..soft@online.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..soft@online.ee');
SELECT   email  FROM customer WHERE  email!='' and email IS NOT NULL and
TRIM(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)$$_$