Sender: owner-postgres95@postgres.Berkeley.EDU
X-Return-Path: andrew_yu
Received: from methi.ndim.edrc.cmu.edu (METHI.NDIM.EDRC.CMU.EDU [128.2.214.230]) by nobozo.CS.Berkeley.EDU (8.6.10/8.6.3) with SMTP id OAA24253 for <postgres95@nobozo.cs.berkeley.edu>; Mon, 6 Nov 1995 14:41:03 -0800
Message-Id: <199511062241.OAA24253@nobozo.CS.Berkeley.EDU>
Received: from localhost by methi.ndim.edrc.cmu.edu id aa01790;
          6 Nov 95 17:40 EST
X-Mailer: exmh version 1.6.2 7/18/95
To: jarnott@bridge.com, postgres95@postgres.Berkeley.EDU
Reply-to: rp2y+@CS.cmu.edu
Subject: Re: (fwd) 
References: <199511062133.NAA16504@eden.CS.Berkeley.EDU> 
In-reply-to: Your message of "Mon, 06 Nov 1995 13:33:56 PST."
             <199511062133.NAA16504@eden.CS.Berkeley.EDU> 
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Date: Mon, 06 Nov 1995 17:40:50 -0500
From: Robert Patrick <Robert_Patrick@methi.ndim.edrc.cmu.edu>
Resent-To: postgres95-redist
Resent-Date: Mon, 06 Nov 95 14:41:04 -0800
Resent-From: pglite
Resent-XMts: smtp

> > 
> >         For reasons that are stupid, I need to be able to let users of Web
> > forms that search our Postgres95 databases have an option to search for a
> > whole word or sub-word.  For instance, is somone give "SONG" as a title,
> > then for whole word match, things like "SONGS" and "SONGBOOK" would not
> > return (they would for sub-word).  My question is, how do I form these
> > requests?  Currently, I use LIKE '%user_word%', which works fine for
> > sub-word.  Any help would be highly appreciated.
> > 
> > 
> > bryon
> 
> You may want to try LIKE '% user_word %' .

As implied below, this misses cases where the word is the first word after a 
newline or a tab and the last line before a punctuation mark, tab or newline.

> Also 
> TEXT LIKE '% user_word %' 

This is incorrect, as explained above.

> OR TEXT LIKE 'user_word %' 

This includes words ending in the 'user_word' and excludes the aforementioned 
stuff on the end of the word.

> OR TEXT LIKE '% user_word'  

This includes words beginning with the 'user_word' and excludes the 
aforementioned stuff on the front of the word.

> 
> should be even more accurate.

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

For matching all cases (sub-word and full-word), you do the same as before:

SELECT * 
  FROM foo 
 WHERE foo.bar LIKE '%user_word%'

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

> 
> Now, how would you do the case-insensitive ?
> 

Doing case-insensitive matching is a pain with regular expressions unless you 
can change the call to C library function call (e.g., regexec()) to do 
case-insensitive matching.  For case-insensitive, full-word only matching, it 
would look something like this:

SELECT * 
  FROM foo
  WHERE foo.bar ~ '[^a-zA-Z1-9][uU][sS][eE][rR]_[wW][oO][rR][dD][^a-zA-Z1-9]'

Hope this helps,
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/  ===========



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