Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 10 of 15 total |
like operator and trailing spaces |
Mon, Apr 23 2007 11:04 AM | Permanent Link |
"Harry de Boer" | 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 |
Mon, Apr 23 2007 11:09 AM | Permanent Link |
"Harry de Boer" | 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 > > > |
Mon, Apr 23 2007 12:14 PM | Permanent Link |
Chris Erdal | "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) |
Mon, Apr 23 2007 2:11 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Apr 23 2007 4:44 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Tue, Apr 24 2007 4:48 AM | Permanent Link |
"Harry de Boer" | 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) > |
Wed, Apr 25 2007 3:51 AM | Permanent Link |
Chris Erdal | 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) |
Wed, Apr 25 2007 3:54 AM | Permanent Link |
Chris Erdal | "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) |
Wed, Apr 25 2007 4:55 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Apr 25 2007 10:40 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |