---
url: >-
  /statelake/scripting-reference/data-types/testing-for-null-empty-or-blank-values.md
---

# Testing For Null (empty or blank) Values

The purpose of this page is to explain how to test for *null*, *empty*, and/or *blank* values across the various **Data Types** that are supported by **Statelake**.

Data values in **Statelake** can be:

* fields in Source, Destination, or custom datasets. Please also see Field Class .
* special variables, such as Super Globals. Please also refer to [Variables](/statelake/scripting-reference/variables/).
* user-declared variables. Please refer to [Variables](/statelake/scripting-reference/variables/) for more information.

Each data value will be of a particular **Data Type**, and it is important that the relationship between values and types is understood so that **Statelake** can be configured correctly.

## Simple Data Types

The majority of the **Data Types**, such as **String**, **Memo**, **Integer**, **floating-point**, **fixed-point**, **DateTime**, and **Boolean**, will always have a default non-*null* value. And **String** could only be *empty* or *blank*.

### Integer, Fixed-point, Or Floating-point

An **Integer**, **fixed-point**, or **floating-point** data type variable has a default value of zero (0), and cannot therefore ever be considered to be *null*, *empty* or *blank*.

Please also refer to [Integer (Integer, Cardinal, Int64)](/statelake/scripting-reference/data-types/integer-integer-cardinal-int64-longint) or [Fixed-point (Currency)](/statelake/scripting-reference/data-types/fixed-point-currency), and [Floating-point (Double, Extended)](/statelake/scripting-reference/data-types/floating-point-single-double-extended) for more information.

### Boolean

A **Boolean** variable has a default value of **False**, and can only ever be **True** or **False** so cannot be *null*, *empty* or *blank*.

[Boolean](/statelake/scripting-reference/data-types/boolean) is available for reference.

### DateTime

A **DateTime** data type has a default value of the day that is considered to be day zero (0) - 30 December 1899. As such it will never be *null*, *empty* or *blank*.

Please also see [DateTime (TDateTime)](/statelake/scripting-reference/data-types/datetime-tdatetime) for details.

## String Data Type (Memo)

A **String** data type variable (or **Memo**) is *empty* when it contains no character, i.e. it has a length of zero (0) characters. This will occur when the variable has been declared but not yet accessed, so will be default have a **String** length of 0.

It will be *blank* when it contains one or more space characters, and nothing else. A *blank* **String** can be forced to be an *empty* **String** by using the **Trim** function which removes spaces.

A **String** can be proven to be *empty* using the **Length** function, which returns the number of characters in the given string. **Length** will return **0** if the **String** is *empty*, as shown in the code example that follows using the **String** variable called **mystring**.

```pascal
 //String variable declared but not yet used
  LogInfo('LENGTH DECLARED string = '+IntToStr(Length(mystring)));      //Result is 0 (EMPTY)

 //Set string with 3 spaces
  mystring := '   '
  LogInfo('LENGTH 3 SPACES = '+IntToStr(Length(mystring)));            //Result is 3 (not EMPTY)

 //Trim the spaces from the string
  mystring := Trim(mystring);
  LogInfo('LENGTH TRIM SPACES = '+IntToStr(Length(mystring)));         //Result is 0 (EMPTY)

 //Fill the string with text
  mystring := 'Sooty and Sweep';
  LogInfo('LENGTH 15 WORDS = '+IntToStr(Length(mystring)));            //Result is 15 (not EMPTY)
```

But a **String** variable can never be *null*.

A **Memo** is simply a very large **String** so the rules that apply to a **String** apply to a **Memo**.

Please also refer to [String (String, Widestring)](/statelake/scripting-reference/data-types/string-string-widestring).

## Variant Data Type

**Statelake** makes a lot of use of variables of the **Variant** data type.

But a **Variant** is like a chameleon - the data type will change as soon as a value is assigned to it. Initially, each **Variant** variable will be of the **Variant** data type, which is essentially a data sub-type.

Within a script, a **Variant** that has been declared but is yet to have a value assigned to it, will return as *empty*.

As soon as the first value is assigned to the **Variant** variable, the data type will be derived from the value that is assigned. So if a **String** is assigned to a **Variant**, then its data type will be **String**, and if an **Integer** is assigned to the **Variant**, then the data type will be **Integer**.

* There are a couple of ways of testing whether a **Variant** variable contains a *null* value or a *null* sub-type. The **VarIsNull** function or **VarType** function can be used. All of the following examples will confirm that the variable called **thevariable** contains *null* - **VarIsNull** will return a **True**, and **VarType** will return a **1**.

```pascal
 //Forcing a variable to be NULL to return a 1
  thevariable := null;
  LogInfo('VarType set as NULL = '+IntToStr(VarType(thevariable)));                 //Result is 1

 //Testing TRUE for a NULL variable
  LogInfo('VarIsNull set as NULL = '+BooleanAsString(VarIsNull(thevariable)));      //Result is TRUE

 //Setting the variable to an integer
  thevariable := 7;                                                                 //Variable is not NULL
  LogInfo('VarType set as INTEGER = '+IntToStr(VarType(thevariable)));              //Result is 3

 //Testing FALSE for a NULL variable
  LogInfo('VarIsNull set as INTEGER = '+BooleanAsString(VarIsNull(thevariable)));   //Result is FALSE
```

* And when testing for an *empty* **Variant** type variable that has been declared but not yet accessed, the **VarIsEmpty** function or **VarType** function can be used. Both the following examples will confirm that the variable called **thevariable** is *empty* - **VarIsEmpty** will return a **True**, and **VarType** will return a **0**.

  ```pascal
   //Variable declared but not yet accessed will return 0 when EMPTY
    LogInfo('VarType UNSET = '+IntToStr(VarType(thecounter)));

   //Variable declared but not yet accessed will return TRUE when EMPTY
    LogInfo('VarIsEmpty UNSET = '+BooleanAsString(VarIsEmpty(thecounter)));
  ```

Please note that the function used in this example called **BooleanAsString** is a user-defined custom function declared in the top of the executing script. For details of this function please see [Functions And Procedures](/statelake/scripting-reference/functions-and-procedures/).

Any *empty or* unfilled field in a database are stored with *null* as a default value, so a **Variant** variable that reads an *empty* field pulled from a database by a dataview will return a value of *null*.

A field in a dataset is a more sophisticated type of variable - an "object" which includes some built-in type-conversion routines. .Value is used to get or set the value of the field as a **Variant**.

* The Value property (.Value) of a dataset field object returns the data value of that field as a **Variant** data type.

  * It can be of **Variant** type *null* if the underlying database field value is *null* i.e. no value has been written to that field as yet so it has defaulted to **null**.
  * It will be of **Variant** type **String** if the underlying database column is a **String** type (e.g. varchar or nvarchar) and a string value (including an empty string) has been assigned to that column in that row in the database i.e. spaces or text have been written to that particular record field in the database.
  * It will be of **Variant** type **Integer** if the underlying database column is an **Integer** type (e.g. int) and a value has been assigned to that column in that row in the database i.e. a whole number has been written to that particular record field in the database.

* The data value contained within the field object can be forced to a particular data type by using the .AsString, .**AsInteger**, and .AsFloat properties of the field object. So instead of using .Value, reference the field using either .AsString, AsInteger, or .AsFloat to have null values converted into a blank **String** , zero (0), or 0.000 respectively.

But be mindful that a null value is different from a blank **String**.

The following example illustrates this using **ANY_DATAVIEW** to refer to a dataview in your configuration, and **Any_Field** to refer to a field within that same dataview.

```pascal
procedure ScriptEvent (var Value : variant);
var
  myVariant : variant;
  myString: string;
  myInteger: integer;
begin
  myVariant := ANY_DATAVIEW['Any_Field'].Value;         // .Value returns a Variant
  if VarIsNull(ANY_DATAVIEW['Any_Field'].Value) then //If the field is NULL
    LogInfo('Any_Field has a Null value');
  myString := ANY_DATAVIEW['Any_Field'].AsString;       // .AsString forces conversion to a string type
  if Trim(ANY_DATAVIEW['Any_Field'].AsString) = '' then // Note: using .AsString and the Trim function
    LogInfo('Any_Field is either Null, an empty string, or a string of one or more spaces');
  if ANY_DATAVIEW['Any_Field'].AsInteger = 0 then //.AsInteger will fail if value is not Null or numeric
    LogInfo('Any_Field is either Null, or zero');
end;
```

The following code uses **VarType**, VarIsNull, and **VarIsEmpty** to test the contents of a database field.

```pascal
 //Testing an "empty" database field to return a value of 1 if NULL
  thevariable := DR_TRANS['CUSTORDERNO'].Value;                                //This field is NULL
  LogInfo('VarType DB NULL = '+IntToStr(VarType(thevariable)));                //Result is 1
  LogInfo('VarIsNull DB NULL = '+BooleanAsString(VarIsNull(thevariable)));     //Result is TRUE
  LogInfo('VarIsEmpty DB NULL = '+BooleanAsString(VarIsEmpty(thevariable)));   //Result is FALSE

 //Testing a "non-empty" database field
  thevariable := DR_TRANS['ADDRESS1'].Value;                                      //Field is not NULL
  LogInfo('VarType DB not NULL = '+IntToStr(VarType(thevariable)));               //Result is 256
  LogInfo('VarIsNull DB not NULL = '+BooleanAsString(VarIsNull(thevariable)));    //Result is FALSE
  LogInfo('VarIsEmpty DB not NULL = '+BooleanAsString(VarIsEmpty(thevariable)));  //Result is FALSE
  LogInfo('');
```

The results from the above code show that the empty database field returns a **1** from **VarType** indicating it is null, so it also returns **True** from **VarIsNull** and **False** from **VarIsEmpty** - it cannot obviously be *null* and also be *empty*.

The code also shows that the *non-empty* database field contains a **String** - as indicated by the result of **256** from **VarType**. It is also therefore, neither *null* or *empty* so both these values return **False**.

For full details about these **Statelake** functions please refer to [Variant Functions](/statelake/scripting-reference/functions-and-procedures/variant-functions/).