The BDE's local SQL appears to have case-sensitive LIKE handling... For
example, I have a field that I wish to look for words in; I might use an SQL
statement like this:

select * from product where description like "%paper%";

.. hoping to get all products with paper anywhere in the description.  
Instead, I miss ones that say "Paper towels" or "PAPER, 8.5 x 11".

Ideas?  Is this behavious acceptable under the SQL standards?  Should I just
forget it and write my own table-scanning routine?

-Kyle

*****  Kyle Cordes @ Automation Service  *****  kcor...@mo.net  *****

I may be wrong here, but any SQL I've ever used was indeed case-sensitive.
Usually that's a good thing...  

However, for what you want to do its gonna cost you some performance
as you'll have to try something like the following:

   select *
   from product
   where upper(desc) like '%PAPER%';

That little upper(desc) may (will?) impact performance significantly
- depending upon your database engine.
I know that in the Oracle world it throws out your indexes!

hth,

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Chris Kasten
Programmer/Analyst

#include <std/disclaimer.h>
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Chris,
I think 'LIKE' is like 'LIKE' and not like 'IS'. I have the same problem
as Kyle and it started when I recompiled my Delphi 1-EXES in Delphi 2.
Before the LIKE-Operator (as it shold be) was not case-sensitive, but
now it is. It seems to me one of many newly implemented bugs in this
overhastened version. Your advice, sadly, works only if you don't seek
in blob-fields. I wonder if somebody has an idea for a complete
workaround. Or should we wait for the first patch of BORLAND?
So long
Roland

Chris,
I think 'LIKE' is like 'LIKE' and not like 'IS'. I have the same problem
as Kyle and it started when I recompiled my Delphi 1-EXES in Delphi 2.
Before the LIKE-Operator (as it shold be) was not case-sensitive, but
now it is. It seems to me one of many newly implemented bugs in this
overhastened version. Your advice, sadly, works only if you don't seek
in blob-fields. I wonder if somebody has an idea for a complete
workaround. Or should we wait for the first patch of BORLAND?
So long
Roland

Yes, but '%PAPER%' already does that, so it's of no further influence.

Jasper

PS: please take into consideration
- when replying, I just think I know;
- when asking, be sure that I don't.

Well, it's my understanding that LIKE is just syntax and introduces
the wild-cards...

... and in that case, THAT was a bug...

Try retrieving some rows from (Oracle) ALL_TABLES where owner like
'%system%': zero!

Jasper

PS: please take into consideration
- when replying, I just think I know;
- when asking, be sure that I don't.

Oops, mea culpa...

Of course you're right.  I've gotten so used to bringing up the fact
that
    upper(col) = 'MYVAL'  (note the =, not like )

will trash indexes that I didn't even think that this one begins with
'like'.  So yeah, you're right - wildcards are good way to *also* ensure
that you will use no indexes.

Thanks,

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Chris Kasten
Programmer/Analyst

#include <std/disclaimer.h>
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

As far as I know, SQL's LIKE is case sensitive.  Just talking about
that issue today for a project our company's working on.  Sadly, it's
in Powerbuilder.  It would be nice if it wasn't but, oh well.