Обсуждение: > and >= give the same result
Hi list, I'm trying to help a friend with his queries and I've something strange (well, to me:): SELECT * FROM tb1 WHERE note > 'y'; returns the *same* result as: SELECT * FROM tb1 WHERE note >= 'y'; There's something wrong: I though I'd have: 1st qry: z% 2nd qry: y% and z% What is wrong with using '>' &| '>=' with strings? I also know there's a page talking about that in the docs, but I can't find it. JY -- Help Stamp Out Rape! (Say Yes.)
On 12/29/11 08:15, Jean-Yves F. Barbier wrote: > Hi list, > > I'm trying to help a friend with his queries and I've something > strange (well, to me:): > > SELECT * FROM tb1 WHERE note> 'y'; > > returns the *same* result as: > > SELECT * FROM tb1 WHERE note>= 'y'; > > There's something wrong: I though I'd have: > 1st qry: z% > 2nd qry: y% and z% > > What is wrong with using '>'&| '>=' with strings? > > I also know there's a page talking about that in the docs, but I > can't find it. The string 'y%' is greater than 'y'! Therefore, both queries will return y% and z% - where is the problem?
On Thu, 29 Dec 2011 08:48:37 -0500
Frank Bax <fbax@sympatico.ca> wrote:
>
> The string 'y%' is greater than 'y'! Therefore, both queries will return
> y% and z% - where is the problem?
Well, I hoped '>' would have returned all rows beginning by 'z'
and not any 'y'.
--
Love sometimes expresses itself in sacrifice.
-- Kirk, "Metamorphosis", stardate 3220.3
On 12/29/11 09:29, Jean-Yves F. Barbier wrote: > On Thu, 29 Dec 2011 08:48:37 -0500 > Frank Bax<fbax@sympatico.ca> wrote: >> >> The string 'y%' is greater than 'y'! Therefore, both queries will return >> y% and z% - where is the problem? > > Well, I hoped '>' would have returned all rows beginning by 'z' > and not any 'y'. WHERE substr(note,0,1)>'y'
On Thu, 29 Dec 2011 08:48:37 -0500
Frank Bax <fbax@sympatico.ca> wrote:
>
> The string 'y%' is greater than 'y'! Therefore, both queries will return
> y% and z% - where is the problem?
Oops, I'm read too fast; but it dont change my PB:
SELECT * FROM table WHERE col > 'Y%' still returns y% & Y%
--
Repartee is something we think of twenty-four hours too late.
-- Mark Twain
On Thu, 29 Dec 2011 10:16:53 -0500 Frank Bax <fbax@sympatico.ca> wrote: > On 12/29/11 09:29, Jean-Yves F. Barbier wrote: > > On Thu, 29 Dec 2011 08:48:37 -0500 > > Frank Bax<fbax@sympatico.ca> wrote: > >> > >> The string 'y%' is greater than 'y'! Therefore, both queries will return > >> y% and z% - where is the problem? > > > > Well, I hoped '>' would have returned all rows beginning by 'z' > > and not any 'y'. > > > WHERE substr(note,0,1)>'y' Nope: comgest=> SELECT * FROM tst1m WHERE substr(note,0,1) > 'y' ORDER BY name; id | name | note ----+------+------ (0 ligne) comgest=> SELECT * FROM tst1m WHERE substr(note,0,1) > 'y%' ORDER BY name; id | name | note ----+------+------ (0 ligne) comgest=> SELECT * FROM tst1m WHERE note ILIKE 'z%' ORDER BY name; ... (2567 lignes) -- PUNK ROCK!! DISCO DUCK!! BIRTH CONTROL!!
On 12/29/11 10:19, Jean-Yves F. Barbier wrote:
> On Thu, 29 Dec 2011 08:48:37 -0500
> Frank Bax<fbax@sympatico.ca> wrote:
>
>>
>> The string 'y%' is greater than 'y'! Therefore, both queries will return
>> y% and z% - where is the problem?
>
> Oops, I'm read too fast; but it dont change my PB:
> SELECT * FROM table WHERE col> 'Y%' still returns y%& Y%
>
NO. It does not.
create table barbier (col varchar);
insert into barbier values ('y%');
insert into barbier values ('z%');
insert into barbier values ('Y%');
select * from barbier where col>'Y%';
col
-----
y%
z%
(2 rows)
On Thu, 29 Dec 2011 10:39:41 -0500
Frank Bax <fbax@sympatico.ca> wrote:
> > Oops, I'm read too fast; but it dont change my PB:
> > SELECT * FROM table WHERE col> 'Y%' still returns y%& Y%
> >
>
> NO. It does not.
>
> create table barbier (col varchar);
> insert into barbier values ('y%');
> insert into barbier values ('z%');
> insert into barbier values ('Y%');
> select * from barbier where col>'Y%';
> col
> -----
> y%
> z%
> (2 rows)
Do you mean that>'Y%' is taken as a literal and don't
develop as Y* (contrary to LIKE 'Y%')?
Meaning 'Y%' was for me: 'Yahoo', 'Ybsd', 'Ycommerce', 'Ymail', etc
--
Damn, I need a Coke!
-- Dr. William DeVries
[after implanting the first artificial human heart]
Jean-Yves F. Barbier wrote on 29.12.2011 17:04: > Do you mean that>'Y%' is taken as a literal and don't > develop as Y* (contrary to LIKE 'Y%')? > > Meaning 'Y%' was for me: 'Yahoo', 'Ybsd', 'Ycommerce', 'Ymail', etc > % is only a wildcard when used with the LIKE operator.
On Thu, 29 Dec 2011 17:17:08 +0100
Thomas Kellerer <spam_eater@gmx.net> wrote:
>
> % is only a wildcard when used with the LIKE operator.
Haaaa, now I understand the results, thanks Thomas!
--
A man who cannot seduce men cannot save them either.
-- Soren Kierkegaard