Re: newbie: Column CHECK(col contains '@') ?

[Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz>]

On Mon, 2002-05-13 at 11:21, john-paul delaney wrote:
> Thanks Joel... that did the trick (even better than I had asked for).  
> Forgive my ignorance, but it your solution a regular expression? 
> 
> Can anyone suggest a good source where I can read up on these (regex's)
> in relation to postgresql?

The PostgreSQL manual has a section (section 4.6) on pattern matching
using REGEX and pattern matching using the SQL 'LIKE' operator.

The LIKE operator (which was what Joel used in his solution for you)
uses '%' as a wildcard and _ as a single character match.

Regex is much more complicated, and there are many sources of help for
it out on the internet.  A similar check using a regex operator would be
something like:

(em ~ '@.*\.')

since there is no need to specify leading and trailing wildcards within
a regex (instead you specify that you want to anchor the regex to the
beginning and/or ending of the string).

A search on the internet might provide a more thorough regex for
validation of e-mail addresses.  A slightly more complex one I have used
is:

(em ~* '^[^(at)]+@[a-z0-9-]+\.[a-z]+')

which should validate (a) there is only a single '@' in the address and
(b) the first part of the domain name contains only valid domain-name
like characters.  The ~* operator is the case insensitive regex match
which I didn't use in the one above since there was no alphabetic
matching involved.

How I do this in my own applications is actually to implement a function
for valid email addresses, viz:

CREATE FUNCTION valid_email(TEXT) RETURNS BOOLEAN AS '
  DECLARE
    email ALIAS FOR $1;
    user TEXT;
    domain TEXT;
  BEGIN
    IF email !~ ''(dot)(at)(dot)'' THEN
      RETURN FALSE; -- One @ good
    END IF;
    IF email ~ ''@.*@'' THEN
      RETURN FALSE; -- Two @s bad
    END IF;
    domain := substring( email from position( ''@'' in email) + 1 );
    user   := substring( email from 1 for position( ''@'' in email) - 1
);
    IF domain ~* ''([a-z0-9-]+\.)+([a-z])?[a-z][a-z]$'' THEN
      -- Only really worth validating the domain
      RETURN TRUE;
    END IF;
    RETURN FALSE;
  END;
' LANGUAGE 'plpgsql';

This checks for internal spaces as well, and means that the rules for
valid e-mail addresses happens in only one place.

Regards,
					Andrew.
> 
> thanks again,
> /j-p.
> 
> 
> On Sun, 12 May 2002, Joel Burton wrote:
> 
> > > One column in my table contains email addresses - I want to check
> > > that any value entered contains a '@'.  How do I create a
> > > CONSTRAINT or CHECK to ensure this when creating the table?
> > 
> > create table em (
> >   em text constraint is_email check (em like '%(at)%(dot)%')
> > );
> > 
> > will work fine, assuming that this check (something @ something . something)
> > is acceptable in your context as "looks like an email address"
> > 
> > - J.
> > 
> > Joel BURTON | joel(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
> > Knowledge Management & Technology Consultant
> > 
> 
> 
> -----------------------
>  JUSTATEST Art Online
>   www.justatest.com
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
> 
-- 
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267
       Are you enrolled at http://schoolreunions.co.nz/ yet?