Thread: 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

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

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

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

How about  using the  contrib/tsearch  module <br />that uses gist indexes. <p>moreover it has  builtin support for
wordstemming,morphology <br />and stuff. (to my knowledge) <p>you would ofcourse require to upgrade to PGSQL7.2 <br /> 
<p>excreptsfrom README.tsearch contrib module. <br />================ <br /><tt>and now you can search all titles with
words'patch' and 'gist':</tt><br /><tt>  select title from titles where titleidx ##
'patch&gist';</tt><tt></tt><p><tt>Here,## is a new operation defined for type 'txtidx' which could use
index</tt><br/><tt>(if exists) built on titleidx. This operator uses morphology to</tt><br /><tt>expand query,
i.e.</tt><br/><tt>  ## 'patches&gist' will find titles with 'patch' and 'gist' also.</tt><br /><tt>If you want to
providequery as is, use operator @@ instead:</tt><br /><tt>  select title from titles where titleidx @@
'patch&gist';</tt><br/>  <br />  <p>"Samuel J. Sutjiono" wrote: <blockquote type="CITE"><style></style><font
face="Arial"><fontsize="-1">This expression matches the word socks or shoes or nike in product
category</font></font><b><fontface="Arial"><font size="-1">where productdescr ~*
'(socks|shoes|nike)'</font></font></b> <fontface="Arial"><font size="-1">Does anybody know what the expression should
be if I want to do <b>'and'</b> of those key words instead of <b>'or'</b> ?</font></font> <font face="Arial"><font
size="-1">Thanks,</font></font><fontface="Arial"><font size="-1">Sam</font></font></blockquote> 

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

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