Board index » delphi » joker char "*", "%"

joker char "*", "%"

Hi to all database freaks,

I 'm trying to use the joker char "%" on Dbase fields which are empty and
filled.

Using the statement e.g.: >> select * from addres where field like '%' <<
delivers only records where "field" is NOT empty.

When using this SQL statement on any SQL database, replacing the from '%' to
'*' the results are a little more different:
I also get records where "field" is empty and of course where "field" is not
empty.

How can I perform the same operation on a DBase database?

The user of my program has to select the value of the column "field" in
order to get some records as result (a kind of filter).

If the user selects "<all>" from a combo box the result should include, as
mentioned above, empty and not empty fields.
If the user select e.g. "cars" from the combo box the result should only
include record where the "field" value is "cars".

Any ideas?

Mike

 

Re:joker char "*", "%"


I'm assuming that you have some program logic which will substitute your
joker character for "<all>" when the user selects that option.  Why not drop
the where clause in that case.

-Jon
Industrial Images, inc.

Re:joker char "*", "%"


I can tell you for sure that Oracle will not select rows with NULL values in
a field when the like operator is used (or any other operator, for that
matter).  I would hope that other DBMS work the same way!  You need to do
one or both of two things:

* When the user selects <all>, entirely remove the WHERE clause that
references the field.

* If you want to perform a like and also return rows where that column is
NULL, add an "or COL is NULL" to your WHERE clause.

V/R
Russell L. Smith

Quote
Michael Fritz wrote in message <73bc4s$5p...@forums.borland.com>...
>Using the statement e.g.: >> select * from addres where field like '%' <<
>delivers only records where "field" is NOT empty.

>When using this SQL statement on any SQL database, replacing the from '%'
to
>'*' the results are a little more different:
>I also get records where "field" is empty and of course where "field" is
not
>empty.

Re:joker char "*", "%"


Thankx,

indeed, I just thought of such kind of solution.

Mike

Jon Claney <*NO*SPAM*...@sprintmail.com> schrieb in Nachricht
<73chbi$6u...@forums.borland.com>...

Quote
>I'm assuming that you have some program logic which will substitute your
>joker character for "<all>" when the user selects that option.  Why not
drop
>the where clause in that case.

>-Jon
>Industrial Images, inc.

Other Threads