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

Wow... and this is the Novice list? Thanks Andrew, you cover it all from a to z. It'll take me a bit of work to digest your message and put it into practice, as the (weird and) wonderful world of regex's is very new to me.

regards
/j-p.

On 13 May 2002, Andrew McMillan wrote:

> 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
> >
>

-----------------------
JUSTATEST Art Online
www.justatest.com