Sender: owner-postgres95@postgres.Berkeley.EDU
Message-Id: <199511072038.MAA31296@nobozo.CS.Berkeley.EDU>
To: "Bryon S. Lape" <blape@utk.edu>
CC: postgres95@postgres.Berkeley.EDU
Reply-to: rp2y+@CS.cmu.edu
Subject: Re: (fwd) 
References: <9511071503.AA00043@aztec.lib.utk.edu> 
In-reply-to: Your message of "Tue, 07 Nov 1995 10:04:41 EST."
             <9511071503.AA00043@aztec.lib.utk.edu> 
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Date: Tue, 07 Nov 1995 15:38:05 -0500
From: Robert Patrick <Robert_Patrick@methi.ndim.edrc.cmu.edu>
Resent-To: postgres95-redist
Resent-Date: Tue, 07 Nov 95 12:38:56 -0800
Resent-From: pglite
Resent-XMts: smtp

I just got back, I'm answering both messages at once.

> >
> >The "real" way to do this is use the '~' operator and a full-blown regular 
> >expression (I had discussed this previously with someone, I thought it was 
> >Bryon Lape).
> 
>         Yes it was me.
> 
>         My next question then is what is faster for full-word searching?
> The three OR's (Things are not case sensitive) or the regular expression?
> Or should I not be concerned with speed at this point?

The "three" OR's do not work for exclusive, full-word searching.

> >
> >For exclusive, full-word only matching, you do something like this:
> >
> >SELECT * 
> >  FROM foo 
> > WHERE foo.bar ~ '[^a-zA-Z1-9]user_word[^a-zA-Z1-9]'
>
>         I tried this and it does not work for whole word searching.  In
> fact, it returns nothing.  Am I missing something??
>         I tried it with fields that contained a single value (call number)
> and a word within a multi-word field (title) and neither returned anything.

Oops, you need to add a couple of things (gross, I know but see the 
explanation below):

 SELECT * 
   FROM foo 
  WHERE foo.bar ~ '.*[^a-zA-Z1-9]user_word[^a-zA-Z1-9].*'
     OR foo.bar ~ 'user_word[^a-zA-Z1-9].*'
     OR foo.bar ~ '.*[^a-zA-Z1-9]user_word'

The problem is that, when doing an exclusive, full-word only search for "foo", 
how do you say this with a single expression (I don't know that it is 
possible):

return this:      "foo is the search string"
but not this:     "barfoo is wrong"

and return this:  "The search string is foo"
but not this:     "foobar is wrong"

and return this:  "The search word foo was found"

The first regex does the third case, the second regex does the first, and the 
third regex does the second.

Cheers,
Robert


===============================================================================
  To unsubscribe from the Postgres95 mailing list, send mail with the subject
  line "DEL" to "postgres95-request@postgres.Berkeley.EDU". 
============  URL: http://s2k-ftp.CS.Berkeley.EDU:8000/postgres95/  ===========