Null values

Topic group Related topics

dBASE supports a special value represented by the keyword null. It is its own data type, and is used to indicate a nonexistent or undefined value. A null value is different from a blank or zero value; null is the absence of a value.

The new DBF7 (dBASE) table type support nulls, as do most other tables, including DB (Paradox). Older DBF formats do not. A null value in a field would indicate that no data has been entered into the field, like in a new row, or that the field has been emptied on purpose. In certain summary operations, null fields are ignored. For example, if you are averaging a numeric field, rows with a null value in the field are ignored. If instead a null value was considered to be zero or some other value, it would affect the average.

null is also used in dBASE to indicate an empty function pointer, a property or variable that is supposed to refer to a function, but doesn’t contain anything.

Null can be used as a value in an expression. In general, if the expression needs to manipulate the value passed to it, and that value is null, the expression will return NULL.

For example:

   ? SIN(0)         // Returns 0.00

   ? SIN(NULL)      // Returns NULL

   ? UPPER("")      // Returns an empty character string

   ? UPPER(NULL)    // Returns NULL

   ? 4 + 0          // Returns 4

   ? 4 + NULL       // Returns NULL

If a function does not need to manipulate the value passed, the NULL will be converted to its default value for the type of argument the function expects.

For example:

   ? TIME()      // Returns the time to whole second

   ? TIME(0)     // Returns the time to hundredths of a second

   ? TIME(NULL)  // Returns the time to hundredths of a second, NULL treated as 0

The following table lists the internal default values for NULL that dBASE supports..

Type               Default value

Bookmark           "" (empty character string)

Character          blank character(s)

Date               {  /  /  } (empty date)

Logical            False

Numeric            0

Unknown            0, "", False, or {  /  /  }

The default data type of NULL is unknown. NULL can be assigned a data type by using it in an expression expecting a certain type, although it retains the value NULL:

x = NULL          // x assigned NULL of unknown type

? TYPE("x")       // Returns "U"

? UPPER(x)        // Returns NULL of character type, x remains unknown type

x = SIN(NULL)     // Returns NULL of numeric type and assigns to x

? TYPE("x")       // Returns "N", x is numeric but still has NULL as value

? UPPER(x)        // Data type mismatch error, UPPER() expects a character type parameter