""Guy Rouillier"" <guyr@xxxxxxxxxxx> wrote in message news:CC1CF380F4D70844B01D45982E671B239E8BE9@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx > Andrus wrote: >> I have a database of e-mail addresses. >> >> I want to select the email addresses which are not valid: >> >> do not contain exactly one @ character, >> contain ; > < " ' , characters or spaces etc. >> >> What is the WHERE clause for this ? > > Please see a long, detailed thread in the archives titled "Email > Verification Regular Expression" on Sept 7, 2005. Guy Rouillier, thank you. I have emails in CHARACTER(60) type columns in database, total 3000 emails. I need to check email addresses for most frequent typos before send. I have only plpgsql language installed, no perl. >From this thread I got the regular expression /^[^@]*@(?:[^@]*\.)?[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)$/How I can use this in where clause ? I havent never used regular expressionsin Postgres.How to exclude top-level domain names from this regex ?Andrus. ---------------------------(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
