Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

SQL Upper w/Like

3 posts in Delphi Last posting was on 2003-09-01 08:46:28.0Z
Brian Posted on 2003-08-29 20:32:43.0Z
From: "Brian" <NoEmail@NoEmail.com>
Newsgroups: Advantage.Delphi
Subject: SQL Upper w/Like
Date: Fri, 29 Aug 2003 15:32:43 -0500
Lines: 22
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4920.2300
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4920.2300
NNTP-Posting-Host: 216.152.27.55
Message-ID: <3f4fb8ec@solutions.advantagedatabase.com>
X-Trace: 29 Aug 2003 14:34:52 -0700, 216.152.27.55
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!216.152.27.55
Xref: solutions.advantagedatabase.com Advantage.Delphi:13817
Article PK: 1107252

Am using D5 Ent v5.01 and ADS/ALS v6.2.

We have a customer that has an ADT that has both upper and mixed case
entries for many of the fields in the table. My SELECT statement is, SELECT
* FROM myTable WHERE UPPER(firstname) LIKE '+QuotedStr(Edit1.Text+'%').

Problem is, the UPPER statement is cause the query to take too long to
return the result (24 seconds).

I have removed the UPPER statement from my query and changed the index to
UPPER(firstname).

Now I cannot seem to get any of the records that are mixed or lower case in
my
result, only upper case. Anyone have any ideas?

Regards,

Brian


Karl Perry Posted on 2003-08-30 02:00:58.0Z
From: "Karl Perry" <kaperry@NOSPAMcablespeed.com>
Newsgroups: Advantage.Delphi
References: <3f4fb8ec@solutions.advantagedatabase.com>
Subject: Re: SQL Upper w/Like
Date: Fri, 29 Aug 2003 19:00:58 -0700
Lines: 38
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: 66.235.30.168
Message-ID: <3f50063b@solutions.advantagedatabase.com>
X-Trace: 29 Aug 2003 20:04:43 -0700, 66.235.30.168
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!66.235.30.168
Xref: solutions.advantagedatabase.com Advantage.Delphi:13820
Article PK: 1107256

Brian,

Leave the UPPER function in the index and in the SQL statement. The SQL
Upper function should map to the index's UPPER function and be able to use
the index.

HTH,

Karl Perry

"Brian" <NoEmail@NoEmail.com> wrote in message
news:3f4fb8ec@solutions.advantagedatabase.com...
> Am using D5 Ent v5.01 and ADS/ALS v6.2.
>
> We have a customer that has an ADT that has both upper and mixed case
> entries for many of the fields in the table. My SELECT statement is,
SELECT
> * FROM myTable WHERE UPPER(firstname) LIKE '+QuotedStr(Edit1.Text+'%').
>
> Problem is, the UPPER statement is cause the query to take too long to
> return the result (24 seconds).
>
> I have removed the UPPER statement from my query and changed the index to
> UPPER(firstname).
>
> Now I cannot seem to get any of the records that are mixed or lower case
in
> my
> result, only upper case. Anyone have any ideas?
>
> Regards,
>
> Brian
>
>
>


Joachim Duerr (ADS Support) Posted on 2003-09-01 08:46:28.0Z
From: "Joachim Duerr (ADS Support)" <jojo.duerr@gmx.de>
Subject: Re: SQL Upper w/Like
Newsgroups: Advantage.Delphi
References: <3f4fb8ec@solutions.advantagedatabase.com> <3f50063b@solutions.advantagedatabase.com>
Organization: Extended Systems GmbH
User-Agent: XanaNews/1.15.6.2
X-Face: ,QMv7[luB)BpWAQ~:"kw6n%0ieY63.:g2K3n~8ky0;||5Xle*Xq+=~<Fy:0CVC2nx@8~vZ
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
NNTP-Posting-Host: 195.2.185.25
Message-ID: <3f52f954@solutions.advantagedatabase.com>
Date: 1 Sep 2003 01:46:28 -0700
X-Trace: 1 Sep 2003 01:46:28 -0700, 195.2.185.25
Lines: 18
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!195.2.185.25
Xref: solutions.advantagedatabase.com Advantage.Delphi:13824
Article PK: 1107259


Karl Perry wrote in <3f50063b@solutions.advantagedatabase.com> :

> Leave the UPPER function in the index and in the SQL statement. The
> SQL Upper function should map to the index's UPPER function and be
> able to use the index.

and translate your search string to uppercase, too:

SELECT
* FROM myTable WHERE UPPER(firstname) LIKE '+QuotedStr(
AnsiUppercase(Edit1.Text)+'%').

--
Joachim Duerr
Lead ADS Support, Extended Systems GmbH, Germany
advantage[AT]extendsys.de
- posted with Xananews 1.15.6.2 -
!!! ADS 7 Beta now available !!!