Board index » delphi » DBAse SQL stmt: "field like '%'"

DBAse SQL stmt: "field like '%'"

Hi,

I'm doing a query on a DBase table which should return a couple of
records.

        "select * from table where field like '%'"

This works fine when there is a value in "field".
If "field" is not filled these few records are missing from the result
set.

If I try this stmt in a "real" SQL database this works better even for
not filled fields?

Why? Or better said why is it not possible to do such things in DBase?

Michael

 

Re:DBAse SQL stmt: "field like '%'"


On Tue, 13 Jul 1999 15:20:23 +0200, Michael Fritz

Quote
<Fritz.Mich...@Kermi.de> wrote:
>I'm doing a query on a DBase table which should return a couple of
>records.

>    "select * from table where field like '%'"

>This works fine when there is a value in "field".
>If "field" is not filled these few records are missing from the result
>set.

You can change the query to:
    select * from table where field is null or field like '%'

Jan

Re:DBAse SQL stmt: "field like '%'"


On Tue, 13 Jul 1999 15:20:23 +0200, Michael Fritz

Quote
<Fritz.Mich...@Kermi.de> wrote:
>    "select * from table where field like '%'"

>This works fine when there is a value in "field".
>If "field" is not filled these few records are missing from the result
>set.

Michael,

My other reply was probably a bit too short to be really helpful.  So
here's a more thorough analysis.

As my other reply demonstrates, an empty string value is considered a
Null value by local SQL.  This is because there is no way in dBase to
make a difference between an empty string and a Null value for a
string field (in contrast with Logical, Numeric and Date fields).

If you do a comparison in SQL with a field containing a Null value,
the result of the comparison is always Null.  The only exceptions are
the special comparison statements 'is null' and 'is not null'.

So far for the 'why'-part of your question.  For a solution, I would
really need more information, but I can make some guesses.

I suppose the query contains actually a parameter instead of the
literal '%'-value?  In that case, you probably want the user to be
able to provide a wildcard search key.  If he types '%', you will want
all records displayed...

That can be achieved by changing the query to:
   select * from table
   where field like :Key or '%' = :Key

Hope this helps (better),

Jan

Other Threads