pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Regular Expression for 'and' instead of 'or'
8+ messages / 6 participants
[nested] [flat]

* Regular Expression for 'and' instead of 'or'
@ 2002-02-22 17:04  Samuel J. Sutjiono <ssutjiono@wc-group.com>
  0 siblings, 4 replies; 8+ messages in thread

From: Samuel J. Sutjiono @ 2002-02-22 17:04 UTC (permalink / raw)
  To: pgsql-sql; pgsql-general@postgresql.org

This expression matches the word socks or shoes or nike in product category 
where productdescr ~* '(socks|shoes|nike)'

Does anybody know what the expression should be  if I want to do 'and' of those key words instead of 'or' ?

Thanks,
Sam 

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Regular Expression for 'and' instead of 'or'
@ 2002-02-22 18:15  Fernando Schapachnik <fschapachnik@vianetworks.com.ar>
  parent: Samuel J. Sutjiono <ssutjiono@wc-group.com>
  3 siblings, 2 replies; 8+ messages in thread

From: Fernando Schapachnik @ 2002-02-22 18:15 UTC (permalink / raw)
  To: Samuel J. Sutjiono <ssutjiono@wc-group.com>; +Cc: pgsql-sql; pgsql-general@postgresql.org

En un mensaje anterior, Samuel J. Sutjiono escribió:
> This expression matches the word socks or shoes or nike in product category 
> where productdescr ~* '(socks|shoes|nike)'
> 
> Does anybody know what the expression should be  if I want to do 'and' of those key words instead of 'or' ?

You have to use something like:

'(socks.*shoes.*nike)|(socks.*nike.*shoes)|...'

where ... is every other possible combination. Not pretty, but that's
a limitation of finite automata (or regular expressions, which are
the same).

Regards.



Fernando P. Schapachnik
Gerente de tecnología de red
y sistemas de información
VIA NET.WORKS ARGENTINA S.A.
fschapachnik@vianetworks.com.ar
Tel.: (54-11) 4323-3381



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: [SQL] Regular Expression for 'and' instead of 'or'
@ 2002-02-22 18:34  Samuel J. Sutjiono <ssutjiono@wc-group.com>
  parent: Fernando Schapachnik <fschapachnik@vianetworks.com.ar>
  1 sibling, 1 reply; 8+ messages in thread

From: Samuel J. Sutjiono @ 2002-02-22 18:34 UTC (permalink / raw)
  To: Fernando Schapachnik <fschapachnik@vianetworks.com.ar>; +Cc: pgsql-sql; pgsql-general@postgresql.org

Thank you very much Fernando.  Do you know whether this expression (regex)
use index scan ?

Regards,
Sam
----- Original Message -----
From: "Fernando Schapachnik" <fschapachnik@vianetworks.com.ar>
To: "Samuel J. Sutjiono" <ssutjiono@wc-group.com>
Cc: <pgsql-sql@postgresql.org>; <pgsql-general@postgresql.org>
Sent: Friday, February 22, 2002 1:15 PM
Subject: Re: [SQL] [GENERAL] Regular Expression for 'and' instead of 'or'


> En un mensaje anterior, Samuel J. Sutjiono escribió:
> > This expression matches the word socks or shoes or nike in product
category
> > where productdescr ~* '(socks|shoes|nike)'
> >
> > Does anybody know what the expression should be  if I want to do 'and'
of those key words instead of 'or' ?
>
> You have to use something like:
>
> '(socks.*shoes.*nike)|(socks.*nike.*shoes)|...'
>
> where ... is every other possible combination. Not pretty, but that's
> a limitation of finite automata (or regular expressions, which are
> the same).
>
> Regards.
>
>
>
> Fernando P. Schapachnik
> Gerente de tecnología de red
> y sistemas de información
> VIA NET.WORKS ARGENTINA S.A.
> fschapachnik@vianetworks.com.ar
> Tel.: (54-11) 4323-3381
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: [SQL] Regular Expression for 'and' instead of 'or'
@ 2002-02-22 18:46  Fernando Schapachnik <fschapachnik@vianetworks.com.ar>
  parent: Samuel J. Sutjiono <ssutjiono@wc-group.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Fernando Schapachnik @ 2002-02-22 18:46 UTC (permalink / raw)
  To: Samuel J. Sutjiono <ssutjiono@wc-group.com>; +Cc: pgsql-sql; pgsql-general@postgresql.org

En un mensaje anterior, Samuel J. Sutjiono escribió:
> Thank you very much Fernando.  Do you know whether this expression (regex)
> use index scan ?

Not sure, but probably an EXPLAIN will tell.

Good luck.

Fernando P. Schapachnik
Gerente de tecnología de red
y sistemas de información
VIA NET.WORKS ARGENTINA S.A.
fschapachnik@vianetworks.com.ar
Tel.: (54-11) 4323-3381



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Regular Expression for 'and' instead of 'or'
@ 2002-02-22 20:20  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Fernando Schapachnik <fschapachnik@vianetworks.com.ar>
  1 sibling, 0 replies; 8+ messages in thread

From: Tom Lane @ 2002-02-22 20:20 UTC (permalink / raw)
  To: Fernando Schapachnik <fschapachnik@vianetworks.com.ar>; +Cc: Samuel J. Sutjiono <ssutjiono@wc-group.com>; pgsql-sql; pgsql-general@postgresql.org

Fernando Schapachnik <fschapachnik@vianetworks.com.ar> writes:
> You have to use something like:

> '(socks.*shoes.*nike)|(socks.*nike.*shoes)|...'

> where ... is every other possible combination. Not pretty, but that's
> a limitation of finite automata (or regular expressions, which are
> the same).

Seems a lot easier to do

	WHERE field ~* 'socks' AND field ~* 'shoes' AND field ~* 'nike'

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: [SQL] Regular Expression for 'and' instead of 'or'
@ 2002-02-22 20:45  Peter Eisentraut <peter_e@gmx.net>
  parent: Samuel J. Sutjiono <ssutjiono@wc-group.com>
  3 siblings, 0 replies; 8+ messages in thread

From: Peter Eisentraut @ 2002-02-22 20:45 UTC (permalink / raw)
  To: Samuel J. Sutjiono <ssutjiono@wc-group.com>; +Cc: pgsql-sql; pgsql-general@postgresql.org

Samuel J. Sutjiono writes:

> This expression matches the word socks or shoes or nike in product category
> where productdescr ~* '(socks|shoes|nike)'
>
> Does anybody know what the expression should be if I want to do 'and'
> of those key words instead of 'or' ?

You probably want something like

productdescr ~* 'socks' AND productdescr ~* 'shoes' AND productdescr ~* 'nike'

I don't think it's possible to do this for the general case with just a
regular expression.

-- 
Peter Eisentraut   peter_e@gmx.net




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Regular Expression for 'and' instead of 'or'
@ 2002-02-22 21:26  Medi Montaseri <medi@cybershell.com>
  parent: Samuel J. Sutjiono <ssutjiono@wc-group.com>
  3 siblings, 0 replies; 8+ messages in thread

From: Medi Montaseri @ 2002-02-22 21:26 UTC (permalink / raw)
  To: Samuel J. Sutjiono <ssutjiono@wc-group.com>; +Cc: pgsql-sql; pgsql-general@postgresql.org

Based on generic rules of Regular Expression, the default is a AND.
For example

RE-1RE-2RE-3 is really (RE-1) and (RE-2) and (RE-3). However Regular
Expression
also provides the "Alternation Metachar" to change it from a default AND
to a OR.

The problem however is if you have target-1 junk target-2 junk junk
target-3
Then the simple (target-1target-2target-3) would not work, as you are
implying
they are following each other. So the fix could be

productdescr ~* '(socks)' AND productdescr ~* '(shoes)' AND productdescr
~*(nike)'

Effectively you are creating a composite expression consisting of three
sub-expression.

"Samuel J. Sutjiono" wrote:

> This expression matches the word socks or shoes or nike in product
> categorywhere productdescr ~* '(socks|shoes|nike)' Does anybody know
> what the expression should be  if I want to do 'and' of those key
> words instead of 'or' ? Thanks,Sam

--
-------------------------------------------------------------------------
Medi Montaseri                               medi@CyberShell.com
Unix Distributed Systems Engineer            HTTP://www.CyberShell.com
CyberShell Engineering
-------------------------------------------------------------------------






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Regular Expression for 'and' instead of 'or'
@ 2002-02-23 06:25  Rajesh Kumar Mallah <mallah@trade-india.com>
  parent: Samuel J. Sutjiono <ssutjiono@wc-group.com>
  3 siblings, 0 replies; 8+ messages in thread

From: Rajesh Kumar Mallah @ 2002-02-23 06:25 UTC (permalink / raw)
  To: Samuel J. Sutjiono <ssutjiono@wc-group.com>; pgsql-sql

How about  using the  contrib/tsearch  module
that uses gist indexes.

moreover it has  builtin support for word stemming,morphology
and stuff. (to my knowledge)

you would ofcourse require to upgrade to PGSQL7.2


excrepts from README.tsearch contrib module.
================
and now you can search all titles with words 'patch' and 'gist':
  select title from titles where titleidx ## 'patch&gist';

Here, ## is a new operation defined for type 'txtidx' which could use
index
(if exists) built on titleidx. This operator uses morphology to
expand query, i.e.
  ## 'patches&gist' will find titles with 'patch' and 'gist' also.
If you want to provide query as is, use operator @@ instead:
  select title from titles where titleidx @@ 'patch&gist';



"Samuel J. Sutjiono" wrote:

> This expression matches the word socks or shoes or nike in product
> categorywhere productdescr ~* '(socks|shoes|nike)' Does anybody know
> what the expression should be  if I want to do 'and' of those key
> words instead of 'or' ? Thanks,Sam

^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2002-02-23 06:25 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-22 17:04 Regular Expression for 'and' instead of 'or' Samuel J. Sutjiono <ssutjiono@wc-group.com>
2002-02-22 18:15 ` Fernando Schapachnik <fschapachnik@vianetworks.com.ar>
2002-02-22 18:34   ` Samuel J. Sutjiono <ssutjiono@wc-group.com>
2002-02-22 18:46     ` Fernando Schapachnik <fschapachnik@vianetworks.com.ar>
2002-02-22 20:20   ` Tom Lane <tgl@sss.pgh.pa.us>
2002-02-22 20:45 ` Peter Eisentraut <peter_e@gmx.net>
2002-02-22 21:26 ` Medi Montaseri <medi@cybershell.com>
2002-02-23 06:25 ` Rajesh Kumar Mallah <mallah@trade-india.com>

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