Postgres Pro
Downloads
Home   >   mailing lists

Thread: Help on phrasee matching in WHERE (colum~*'.*ab*.')

Help on phrasee matching in WHERE (colum~*'.*ab*.')

From
Gabor Szilagyi
Date:
Hi,

I am running PostgreSQL 7.0.3 on a Linux 2.4.0 using PHP4 (that is might
not relevant here).

What I am running into is this.

I am trying to pick out rows from the database which contains a
particular string.

I am using the following

SELECT * FROM in_table WHERE n_from ~* '.*nb*.

(Well actually a bunch of this condition is AND/OR -ed together)

My trouble is this: If I enter only a two character string as above,
it will not only matches string containing "nb" but all those which
contains the letter "n".

I tried this even with pgaccess directly !!! Same thing, is this a bug?

It is _very_ important for met to be able to match words containing
same strings including the two character variety ...

The above behaivour is just doesn't cut it..

Any suggestion would be welcomed

Gabor Szilagyi
szilagyi@nyo.unep.org

Re: Help on phrasee matching in WHERE (colum~*'.*ab*.')

From
"Rob Arnold"
Date:
It's acting correctly.  The * means match 0 or more b's

Rewrite it as '.*nb+'  the + means match one or more times.

--rob


----- Original Message -----
From: "Gabor Szilagyi" <Gabor.Szilagyi@nyo.unep.org>
To: <pgsql-novice@postgresql.org>
Sent: Thursday, March 08, 2001 2:15 PM
Subject: Help on phrasee matching in WHERE (colum~*'.*ab*.')


> Hi,
>
> I am running PostgreSQL 7.0.3 on a Linux 2.4.0 using PHP4 (that is might
> not relevant here).
>
> What I am running into is this.
>
> I am trying to pick out rows from the database which contains a
> particular string.
>
> I am using the following
>
> SELECT * FROM in_table WHERE n_from ~* '.*nb*.
>
> (Well actually a bunch of this condition is AND/OR -ed together)
>
> My trouble is this: If I enter only a two character string as above,
> it will not only matches string containing "nb" but all those which
> contains the letter "n".
>
> I tried this even with pgaccess directly !!! Same thing, is this a bug?
>
> It is _very_ important for met to be able to match words containing
> same strings including the two character variety ...
>
> The above behaivour is just doesn't cut it..
>
> Any suggestion would be welcomed
>
> Gabor Szilagyi
> szilagyi@nyo.unep.org
>
>

Re: Help on phrasee matching in WHERE (colum~*'.*ab*.')

From
"Oliver Elphick"
Date:
Gabor Szilagyi wrote:
  >I am trying to pick out rows from the database which contains a
  >particular string.
  >
  >I am using the following
  >
  >SELECT * FROM in_table WHERE n_from ~* '.*nb*.
  >
  >(Well actually a bunch of this condition is AND/OR -ed together)
  >
  >My trouble is this: If I enter only a two character string as above,
  >it will not only matches string containing "nb" but all those which
  >contains the letter "n".

I think you are slightly confused about how pattern-matching works.

Your pattern match says "0-many characters followed by `n' followed by
0-many `b's".  That is why `n' alone matches it.

If you simply want any string containing `nb' (case-insensitive)
use "n_from ~* 'nb'".  You don't have to say anything at all about
the parts of the string you are not interested in.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Who is like Thee among the gods, O Lord? Who is like
      Thee, majestic in holiness, awesome in praises,
      working wonders?"
                              Exodus 15:11