I store some date into table. Let say, customers.(name, adress...).
Then I use query to "find" something with "select - LIKE". How should I
solve case-sensitive problem in order of the best performance? Should I
force only upper-case input or I sould allow both and then what?
please, advice
Andrej

Andrej,

<< I store some date into table. Let say, customers.(name, adress...).  Then
I use query to "find" something with "select - LIKE". How should I solve
case-sensitive problem in order of the best performance? >>

LIKE is always un-optimized if you're doing a complete wildcard search
('%<Text>%'), so the only way to do it is this:

UPPER(MyColumn) LIKE '%TEXT%'

--
Tim Young
Elevate Software
www.elevatesoft.com

Ok. But, what is better from the point of view of performance? Is it better
to allow only uppercase input? or is it better to allow both and then use
UPPER(MyColumn) LIKE '%TEKST%'. ?
Andrej

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:4F3586A2-7EED-4DED-9DF6-466C346F62DC@news.elevatesoft.com...
> Andrej,
>
> << I store some date into table. Let say, customers.(name, adress...).
> Then I use query to "find" something with "select - LIKE". How should I
> solve case-sensitive problem in order of the best performance? >>
>
> LIKE is always un-optimized if you're doing a complete wildcard search
> ('%<Text>%'), so the only way to do it is this:
>
> UPPER(MyColumn) LIKE '%TEXT%'
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

Tim


I thought it was

UPPER(MyColumn) LIKE Upper('%Text%')

Roy Lambert

Roy,

<< I thought it was

UPPER(MyColumn) LIKE Upper('%Text%') >>

It doesn't matter for wildcards on both ends of the search string.

--
Tim Young
Elevate Software
www.elevatesoft.com

Andrej,

<< Ok. But, what is better from the point of view of performance? Is it
better to allow only uppercase input? or is it better to allow both and then
use UPPER(MyColumn) LIKE '%TEKST%'. ? >>

It doesn't matter - with wildcards on both ends of the search string, the
search is always un-optimized.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tnx. But what do you suggest? The performance is very important. So, what is
the best approach?
Is there something to do with indexes?
Andrej

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:F9A9CC5A-B252-4D88-A716-7079FA58B625@news.elevatesoft.com...
> Andrej,
>
> << Ok. But, what is better from the point of view of performance? Is it
> better to allow only uppercase input? or is it better to allow both and
> then use UPPER(MyColumn) LIKE '%TEKST%'. ? >>
>
> It doesn't matter - with wildcards on both ends of the search string, the
> search is always un-optimized.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

Andrej,

<< Tnx. But what do you suggest? The performance is very important. So, what
is the best approach?  Is there something to do with indexes? >>

The only way to optimize it would be to not use wildcards at both ends of
the search string.   Are you able to change the search in that manner ?

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in
news:8A0C23E0-8E65-4331-80E1-42C207E140EF@news.elevatesoft.com:

> The only way to optimize it would be to not use wildcards at both ends
> of the search string.   Are you able to change the search in that
> manner ?

Tim, I've not yet implemented full-text indexes, but if Andrej wants to
search for bits of names or addresses could he optimise things by searching
for TEKST% ? (assuming his users can always look for the words STARTING
with something, albeit in the middle of the field's text)

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 4 + EDB 1.04 build 3)

Chris,

<< Tim, I've not yet implemented full-text indexes, but if Andrej wants to
search for bits of names or addresses could he optimise things by searching
for TEKST% ? (assuming his users can always look for the words STARTING
with something, albeit in the middle of the field's text) >>

Yes, that would be fast if there was a case-insensitive index on the column
being searched and he worded the search like this:

UPPER(MyColumn) LIKE UPPER('TEST%')

--
Tim Young
Elevate Software
www.elevatesoft.com