pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRegular 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>
2002-02-22 18:15 ` Re: Regular Expression for 'and' instead of 'or' Fernando Schapachnik <fschapachnik@vianetworks.com.ar>
2002-02-22 20:45 ` Re: [SQL] Regular Expression for 'and' instead of 'or' Peter Eisentraut <peter_e@gmx.net>
2002-02-22 21:26 ` Re: Regular Expression for 'and' instead of 'or' Medi Montaseri <medi@cybershell.com>
2002-02-23 06:25 ` Re: Regular Expression for 'and' instead of 'or' Rajesh Kumar Mallah <mallah@trade-india.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 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 ` Re: [SQL] Regular Expression for 'and' instead of 'or' Samuel J. Sutjiono <ssutjiono@wc-group.com>
2002-02-22 20:20 ` Re: Regular Expression for 'and' instead of 'or' Tom Lane <tgl@sss.pgh.pa.us>
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 17:04 Regular Expression for 'and' instead of 'or' Samuel J. Sutjiono <ssutjiono@wc-group.com>
2002-02-22 18:15 ` Re: Regular Expression for 'and' instead of 'or' Fernando Schapachnik <fschapachnik@vianetworks.com.ar>
@ 2002-02-22 18:34 ` Samuel J. Sutjiono <ssutjiono@wc-group.com>
2002-02-22 18:46 ` Re: [SQL] Regular Expression for 'and' instead of 'or' 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 17:04 Regular Expression for 'and' instead of 'or' Samuel J. Sutjiono <ssutjiono@wc-group.com>
2002-02-22 18:15 ` Re: Regular Expression for 'and' instead of 'or' Fernando Schapachnik <fschapachnik@vianetworks.com.ar>
2002-02-22 18:34 ` Re: [SQL] Regular Expression for 'and' instead of 'or' Samuel J. Sutjiono <ssutjiono@wc-group.com>
@ 2002-02-22 18:46 ` Fernando Schapachnik <fschapachnik@vianetworks.com.ar>
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 17:04 Regular Expression for 'and' instead of 'or' Samuel J. Sutjiono <ssutjiono@wc-group.com>
2002-02-22 18:15 ` Re: Regular Expression for 'and' instead of 'or' Fernando Schapachnik <fschapachnik@vianetworks.com.ar>
@ 2002-02-22 20:20 ` Tom Lane <tgl@sss.pgh.pa.us>
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 17:04 Regular Expression for 'and' instead of 'or' Samuel J. Sutjiono <ssutjiono@wc-group.com>
@ 2002-02-22 20:45 ` Peter Eisentraut <peter_e@gmx.net>
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 17:04 Regular Expression for 'and' instead of 'or' Samuel J. Sutjiono <ssutjiono@wc-group.com>
@ 2002-02-22 21:26 ` Medi Montaseri <medi@cybershell.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-22 17:04 Regular Expression for 'and' instead of 'or' Samuel J. Sutjiono <ssutjiono@wc-group.com>
@ 2002-02-23 06:25 ` Rajesh Kumar Mallah <mallah@trade-india.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