LS

I have records in table test that ends with 'abc'

I tried:

select * from test
where veld2 not like '%abc'
and veld1 < 100

but the fields that contain the 'abc' at the end are in the result set.

At first I thought there were trailing spaces, so I removed them with UPDATE
test SET veld2 = TRIM(TRAILING ' ' FROM veld2) WHERE veld1 < 100

select * from test
where veld2 not like '%abc%'
and veld1 < 100

gives the right results (records not ending on abc).

Shouldn't the first statement only show records not ending on 'abc'?

Regards, Harry

Tried it with LIKE instead of NOT LIKE

select * from test
where veld2 like '%abc'
and veld1 < 100

gives 0 records, while there should be some!

Regards, Harry


"Harry de Boer" <harry@staaf.nl> schreef in bericht
news:E9DF8638-0453-4C80-8E8B-D141A114B225@news.elevatesoft.com...
> LS
>
> I have records in table test that ends with 'abc'
>
> I tried:
>
> select * from test
> where veld2 not like '%abc'
> and veld1 < 100
>
> but the fields that contain the 'abc' at the end are in the result set.
>
> At first I thought there were trailing spaces, so I removed them with
UPDATE
> test SET veld2 = TRIM(TRAILING ' ' FROM veld2) WHERE veld1 < 100
>
> select * from test
> where veld2 not like '%abc%'
> and veld1 < 100
>
> gives the right results (records not ending on abc).
>
> Shouldn't the first statement only show records not ending on 'abc'?
>
> Regards, Harry
>
>
>

"Harry de Boer" <harry@staaf.nl> wrote in
news:BA912EC7-AA7B-40CE-90F9-326C2AB5713D@news.elevatesoft.com:

> Tried it with LIKE instead of NOT LIKE
>
> select * from test
> where veld2 like '%abc'
> and veld1 < 100
>
> gives 0 records, while there should be some!
>
> Regards, Harry
>
>
> "Harry de Boer" <harry@staaf.nl> schreef in bericht
> news:E9DF8638-0453-4C80-8E8B-D141A114B225@news.elevatesoft.com...
>> LS
>>
>> I have records in table test that ends with 'abc'
>>
>> I tried:
>>
>> select * from test
>> where veld2 not like '%abc'
>> and veld1 < 100
>>
>> but the fields that contain the 'abc' at the end are in the result
>> set.
>>
>> At first I thought there were trailing spaces, so I removed them with
> UPDATE
>> test SET veld2 = TRIM(TRAILING ' ' FROM veld2) WHERE veld1 < 100
>>
>> select * from test
>> where veld2 not like '%abc%'
>> and veld1 < 100
>>
>> gives the right results (records not ending on abc).
>>
>> Shouldn't the first statement only show records not ending on 'abc'?

is veld2 a CHAR field?

As I understand EDB and CHAR/VARCHAR fields, this behaviour is correct
for CHAR fields if they are longer than your texts, and would not happen
if you used varchar fields instead of char fields.

I believe that replacing CHAR with VARCHAR should give you the behaviour
you expected.

The advice from Tim was that VARCHAR is for everyday use, and CHAR is
for specialised use when you really want to have a fixed-length field
with trailing spaces being taken into account.

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 3 + EDB 1.00 build 6)

Chris


Don't forget that unless the behaviour has changed VARCHAR does NOT trim trailing spaces that you or the user has added!

Roy Lambert

Harry,

<< but the fields that contain the 'abc' at the end are in the result set.
>>

It's a bug.  It's converting the expression to a partial-length search when
it shouldn't be.

--
Tim Young
Elevate Software
www.elevatesoft.com

Chris,

As Tim  mentioned: it's a bug.

Either CHAR or VARCHAR with NO trailing spaces sould give the same results
in my opninon.

Regards, Harry

"Chris Erdal" <chris@No-Spam-erdal.net> schreef in bericht
news:Xns991BB92527B2814torcatis@64.65.248.118...
> "Harry de Boer" <harry@staaf.nl> wrote in
> news:BA912EC7-AA7B-40CE-90F9-326C2AB5713D@news.elevatesoft.com:
>
> > Tried it with LIKE instead of NOT LIKE
> >
> > select * from test
> > where veld2 like '%abc'
> > and veld1 < 100
> >
> > gives 0 records, while there should be some!
> >
> > Regards, Harry
> >
> >
> > "Harry de Boer" <harry@staaf.nl> schreef in bericht
> > news:E9DF8638-0453-4C80-8E8B-D141A114B225@news.elevatesoft.com...
> >> LS
> >>
> >> I have records in table test that ends with 'abc'
> >>
> >> I tried:
> >>
> >> select * from test
> >> where veld2 not like '%abc'
> >> and veld1 < 100
> >>
> >> but the fields that contain the 'abc' at the end are in the result
> >> set.
> >>
> >> At first I thought there were trailing spaces, so I removed them with
> > UPDATE
> >> test SET veld2 = TRIM(TRAILING ' ' FROM veld2) WHERE veld1 < 100
> >>
> >> select * from test
> >> where veld2 not like '%abc%'
> >> and veld1 < 100
> >>
> >> gives the right results (records not ending on abc).
> >>
> >> Shouldn't the first statement only show records not ending on 'abc'?
>
> is veld2 a CHAR field?
>
> As I understand EDB and CHAR/VARCHAR fields, this behaviour is correct
> for CHAR fields if they are longer than your texts, and would not happen
> if you used varchar fields instead of char fields.
>
> I believe that replacing CHAR with VARCHAR should give you the behaviour
> you expected.
>
> The advice from Tim was that VARCHAR is for everyday use, and CHAR is
> for specialised use when you really want to have a fixed-length field
> with trailing spaces being taken into account.
>
> --
> Chris
> (XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 3 + EDB 1.00 build 6)
>

Roy Lambert <roy.lambert@skynet.co.uk> wrote in
news:91A3D7A4-6A45-41C0-8C9F-7F85D1F4A0E7@news.elevatesoft.com:

> Chris
>
>
> Don't forget that unless the behaviour has changed VARCHAR does NOT
> trim trailing spaces that you or the user has added!
>

Roy,

Yes, you're right.

But I seem to recall that you will find the right-trimmed version of a text
you're looking for in a VARCHAR key, but not in a CHAR key. (I may be wrong
there)

This is all pretty non-intuitive... :-\

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 3 + EDB 1.00 build 6)

"Harry de Boer" <harry@staaf.nl> wrote in
news:758079F3-1D67-41A0-94ED-64702B68FFD9@news.elevatesoft.com:

> Chris,
>
> As Tim  mentioned: it's a bug.
>

Harry

That's good news!

At least we know Tim'll fix it, and once we get our heads around his way of
doing things it'll be 100% reliable.

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 3 + EDB 1.00 build 6)

Chris


That depends on wether Tim has changed the behaviour or not.

As was 'abc' <> 'abc ' <> 'abc    ' even for a VARCHAR. I added triggers to remove all trailing spaces.


Roy Lambert

Roy,

<< That depends on wether Tim has changed the behaviour or not. >>

I did in 1.02, per the release notes and this incident report:

http://www.elevatesoft.com/scripts/incident.dll?action=viewaddr&category=edb&release=1.02&type=f&incident=2268

--
Tim Young
Elevate Software
www.elevatesoft.com