RE: [HACKERS] SQL92 - Mailing list pgsql-hackers

> > Well, In all of the major Databases that I have worked with there is
> > also the "[character_set]" matching operator.
> > Does anybody know if the "[character_set]" stuff is part of the
> > standard?
>
> Hoping against hope, eh? afaik those are all extensions (though SQL3
> has
> some enhanced functionality with the SIMILAR operator). From my copy
> of
> the second draft standard published in 1992:
>
> <like predicate> uses the triadic operator LIKE (or the inverse,
> NOT LIKE), operating on three character strings and returning
> a Boolean. LIKE determines whether or not a character string
> "matches" a given "pattern" (also a character string). The char-
> acters '%' (percent) and '_' (underscore) have special meaning when
> they occur in the pattern. The optional third argument is a charac-
> ter string containing exactly one character, known as the "escape
> character", for use when a percent or underscore is required in the
> pattern without its special meaning.
>
> What version of Postgres are you running? I vaguely recall some
> complaints and fixes in the sort-of-recent past. Don't know if it
> affected simple patterns or only more complicated stuff.
>
> Can you send a simple
>
>   SELECT text 'your string here' LIKE 'your pattern here';
>
> which doesn't behave as you would expect? That would make this a bit
> less theoretical...
>
>                   - Tom
>
Well, this all started with this message:
>
Since I didn't get any feedback from the list about it I decided to jump
into the code myself.
Upon finding the like.c I looked through the code and found that
"[character_set]"'s were ignored and that the problem expressed in the
above message was not evident in the code.  (btw this is the CVS version
of 6.4; I cvs update daily.)
So, I asked weather the "[character_set]" stuff was in the standard,
which it is not.
And I asked if I should be looking somewhere else for munging of "%%".
(I'm currently looking at src/backend/utils/adt/like.c)

So, that's where I stand.
    -DEJ
CC'd to the PostgreSQL Hackers list.

I performed the same test as Brian on the cvs version of 6.4 and it
exhibits the same behavior.  could we get a fix in for the release.
        -DEJ

> Hehehe...
>
> Try using the "*", as I posted to your early send... see if it makes
> any
> difference.
Doing a like '*' will search for the character '*';

> > er sorry about that last "early send" message...
> >
> > well, at first I though you were correct, but it turns out that
> > postgresql
> > is also inconsistent. Consider a table with a field username. 3
> records:
> >
> > "Brian % Schaffner"
> > "Brian T Schaffner"
> > "%"
> >
> > select * from table where username='%' gets all rows (expected)
> > select * from table where username='%%' gets the row with "%"
> (expected)
> > select * from table where username='%%%' gets the row with "%"
> > (expected)
> > select * from table where username='%%%%' gets no rows (expected)
> > select * from table where username='% %' gets the 2 name rows
> (expected)
> > select * from table where username='% %%' gets the 2 name rows (NOT
> > expected)
> > select * from table where username='%% %' gets no rows (NOT
> expected)
> > select * from table where username='% % %' gets the 2 name rows
> > (expected)
> > select * from table where username='% %% %' gets the 2 name rows
> (NOT
> > expected)
> > select * from table where username='% % % %' gets no rows (expected)
> >
> > so, if %% is the LIKE representation for a literal %, then why does
> '%
> > %%' return
> > the 2 name rows, and '%% %' return no rows, and '% %% %' not return
> the
> > row with the
> > embedded literal %?
> >
> > I could not get postgres to single out the row with the embedded %
> using
> > LIKE.
> >
> > why is this not getting any easier to define?
> >
> > -----Original Message-----
> > From: Daniel J. Lashua [mailto:djl@stftx9.irngtx.tel.gte.com]
> > Sent: Friday, September 25, 1998 10:58 AM
> > To: Brian Schaffner
> > Cc: 'Rasmus Lerdorf'; 'bourbon@bourbon.netvision.net.il';
> > php3@lists.php.net
> > Subject: RE: [PHP3] ARGH!! strstr() changed?
> >
> >
> > On Fri, 25 Sep 1998, Brian Schaffner wrote:
> >
> > > in PostgreSQL (6.3.2 on FreeBSD 2.2.6):
> > >
> > > select * from table where field like '%%';
> > >
> > > returns NO rows;
> > >
> > > select * from table where field like '%';
> > >
> > > returns ALL rows;
> > >
> > > -brian-
> > >
> >
> >
> > I am not in any way doubting what you say... but that doesn't seem
> > right.
> > They should both reuturn all rows. Maybe in Postgres %% is the way
> to
> > state you actually want to search for ONE "%"?
> >
> > Out of curiosity, if you have time, could you test that. Make a
> table
> > with
> > a field and in one row of the table in the field insert "%". Then do
> > your
> > select * from table where somefield LIKE '%%' and see if it returns
> the
> > one row?
> >
> > Am I offbase, or does that sound like incorrect behavior to you too?
> >
> > Daniel
> >
> >
>
>
> --
> PHP 3 Mailing List   http://www.php.net/
> To unsubscribe send an empty message to php3-unsubscribe@lists.php.net
> To subscribe to the digest list:  php3-digest-subscribe@lists.php.net
> For help: php3-help@lists.php.net  Archive:
> http://www.php.net/mailsearch.php3
>