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

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:SQL-stmt: "field like '%'"


Greetings

I have a greater familiarity with InterBase, however, here are a couple
of random thoughts.

Perhaps dBase interprets '%' as "one or more characters" and if there
are no characters ie. an empty string, then it does not select those
records.  InterBase does not return rows where the column contains NULL
because NULL is not an empty string - it is the absence of anything that
can be called a string.  I do not know if dBase has the same
functionality.

You could try something like:

  select * from table where field like '%' or field = ''

Or even this:

  select * from table where field like '%' or field = '' or field is
NULL

hth

Dave

Quote
Michael Fritz wrote:

> 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:SQL-stmt: "field like '%'"


Hi Michael!

On Tue, 13 Jul 1999 15:22:30 +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.

Might be that BDE treats those fields as null instead of blank and
nulls can not be tested with like operator.

tomi.

Other Threads