Sender: owner-postgres95@postgres.Berkeley.EDU
Date: Tue, 7 Nov 1995 17:35:43 -0500
Message-Id: <9511072235.AA01887@medusa.perseus.tufts.edu>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
To: postgres95@postgres.Berkeley.EDU
From: dasmith@perseus.tufts.edu (David Smith)
Subject: Re: Whole-word matching
Resent-To: postgres95-redist
Resent-Date: Tue, 07 Nov 95 14:34:33 -0800
Resent-From: pglite
Resent-XMts: smtp

>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 with this is that you miss the case where user_word is at the
very beginning or end of the field bar. I suggest:

 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$'
     OR foo.bar = 'user_word';


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