pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: Richi Plana <richip@mozcom.com>
To: pgsql-sql@postgreSQL.org
Subject: Re: [GENERAL] Determining if "in a text set"
Date: Sun, 28 Mar 1999 19:24:50 +0200
Message-ID: <l0311070cb3240c3166d7@[147.233.148.142]> (raw)
In-Reply-To: <Pine.LNX.4.04.9903271223140.22809-100000@jaguar.mozcom.com>

I am redirecting the discussion to the SQL list, as it is more appropriate
than the GENERAL list.

At 06:41 +0200 on 27/03/1999, Richi Plana wrote:

> I have a table/class with a text field which contains a single word
> (possibly trailed by whitespaces). Given a phrase (a text literal composed
> of words separated by one or more whitespaces), what's the best way to
> SELECT those tuples with fields containing instances that match exactly
> one of the words in the given string?
>
> Example:
>
> Table:
> Tuple#   ...   FieldN
> 1        ...   'MON'
> 2        ...   'TUE'
> 3        ...   'THURS'
> 4        ...   'THU'
> 5        ...   'FRI'
>
> Given phrase:
> 'MON TUE WED THURS'
> Tuple 1 will match
> Tuple 2 will match
> Tuple 3 will match
> Tuple 4 will NOT match
> Tuple 5 will NOT match
>
> Right now, I'm extracting (using strtok(), 8^P) each word from the given
> phrase and using "SELECT * FROM {class} where FieldN LIKE '%{word}%'", but
> it's terribly slow, relies on strtok() to parse words, and Tuple 4 above
> will match.

First, do without the strtok. You could achieve the same result (that also
matches tuple 4) with:

  SELECT * FROM {class}
  WHERE '{phrase}' LIKE '%' || ( rtrim( FieldN ) || '%' );

Hehe... the strange expression simply cuts off spaces from the right side
of the field, and concatenates % signs on its left and right. So you match
the phrase against the keyword, not the other way around.

It is more readable if you use regular expression rather than 'like':

  SELECT * FROM {class}
  WHERE '{phrase}' ~ rtrim( FieldN );

Now, turning attention to eliminating the fourth tuple from matching: I'm
not an expert on regular expressions. I think postgres does not support
perl's concept of word boundaries. But if the phrase is supposed to contain
just spaces (ascii 32, if you will) as word separators, I'd match against
the field with a space on the left and nothing on the right, a space on the
right and nothing on the left, spaces on both sides, or standing alone:

  SELECT * FROM {class}
  WHERE '{phrase}' ~ ' ' || ( rtrim( FieldN ) || '$' )
     OR '{phrase}' ~ '^' || ( rtrim( FieldN ) || ' ' )
     OR '{phrase}' ~ ' ' || ( rtrim( FieldN ) || ' ' )
     OR '{phrase}' = rtrim( FieldN );

The equivalent with 'like' is:

  SELECT * FROM {class}
  WHERE '{phrase}' LIKE '% ' || rtrim( FieldN )
     OR '{phrase}' LIKE rtrim( FieldN ) || ' %'
     OR '{phrase}' LIKE '% ' || ( rtrim( FieldN ) || ' %' )
     OR '{phrase}' = rtrim( FieldN );

If the spaces between the words in your phrase are not just plain spaces
but can also be tabs etc, you will have to use the regexp version, and
write something like '[ \t\r\f\n]' - though I'm less than sure that
postgres's regular expressions support these. Can anyone tell us which
regexp definition postgres uses?

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





view thread (7+ messages)  latest in thread

Message-ID: <l0311070cb3240c3166d7@[147.233.148.142]>
Permalink:  ../l0311070cb3240c3166d7@%5B147.233.148.142%5D/
Also on:    postgresql.org/message-id/l0311070cb3240c3166d7@[147.233.148.142]

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: herouth@oumail.openu.ac.il, richip@mozcom.com
  Subject: Re: [GENERAL] Determining if "in a text set"
  In-Reply-To: <l0311070cb3240c3166d7@[147.233.148.142]>
  References: <Pine.LNX.4.04.9903271223140.22809-100000@jaguar.mozcom.com> <l0311070cb3240c3166d7@[147.233.148.142]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox