NULL and the Empty String

Use the NULL keyword to indicate that a value is not specified. NULL is always the preferred way in SQL to indicate that a data value is unspecified or nonexistent for any reason.

The SQL zero-length string (empty string) is specified by two single quote characters. The empty string ('') is not the same thing as NULL.

Note:

The SQL zero-length string is not recommended for use as a field input value or a field default value; in ObjectScript, this corresponds to a string of length one that contains the $CHAR(0) character. Use NULL to represent the absence of a data value, which corresponds to the ObjectScript empty string (""). See “ObjectScript and SQL” for more information.

The SQL zero-length string should be avoided in SQL coding. However, because many SQL operations delete trailing blank spaces, a data value that contains only whitespace characters (spaces and tabs) may result in an SQL zero-length string.

Note that different SQL length functions return different values: LENGTH, CHAR_LENGTH, and DATALENGTH return SQL lengths. $LENGTH returns ObjectScript representation length. See The Length of NULL below. LENGTH does not count trailing blank spaces; all other length functions count trailing blank spaces.

NULL Processing

The NOT NULL data constraint requires that a field must receive a data value; specifying NULL rather than a value is not permitted. This constraint does not prevent the use of an empty string value. For further details, refer to the CREATE TABLE command.

The IS NULL predicate in the WHERE or HAVING clause of a SELECT statement selects NULL values; it does not select empty string values.

The IFNULL function evaluates a field value and returns the value specified in its second argument if the field evaluates to NULL. It does not treat an empty string value as a non-NULL value.

The COALESCE function selects the first non-NULL value from supplied data. It treats empty string values as non-NULL.

When the CONCAT function or the concatenate operator (||) concatenate a string and a NULL, the result is NULL. This is shown in the following example:

SELECT {fn CONCAT('fred',NULL)} AS FuncCat,   -- returns <null>
       'fred'||NULL AS OpCat                  -- returns <null>

The AVG, COUNT, MAX, MIN, and SUM aggregate functions ignore NULL values when performing their operations. (COUNT * counts all rows, because there cannot be a record with NULL values for all fields.) The DISTINCT keyword of the SELECT statement includes NULL in its operation; if there are NULL values for the specified field, DISTINCT returns one NULL row.

The AVG, COUNT, and MIN, aggregate functions are affected by empty string values. The MIN function considers an empty string to be the minimum value, even when there are rows that have a value of zero. The MAX and SUM aggregate functions are not affected by empty string values.

NULL in Expressions

Supplying NULL as an operand to most SQL functions returns NULL.

Any SQL arithmetic operation that has NULL as an operand returns a value of NULL. Thus, 7+NULL=NULL. This includes the binary operations addition (+), subtraction (-), multiplication (*), division (/), integer division (\), and modulo (#), and the unary sign operators plus (+) and minus (-).

An empty string specified in an arithmetic operation is treated as a value of 0 (zero). Division (/), integer division (\), or modulo (#) by empty string (6/'') results in a <DIVIDE> error.

The Length of NULL

Within SQL, the length of a NULL is undefined (it returns <null>). The length of an empty string, however, is defined as length zero. This is shown in the following example:

SELECT LENGTH(NULL) AS NullLen,   -- returns <null>
       LENGTH('') AS EmpStrLen    -- returns 0

As shown in this example, the SQL LENGTH function returns the SQL lengths.

You can convert an SQL zero-length string to a NULL by using the ASCII function, as shown in the following example:

SELECT LENGTH(NULL) AS NullLen,                  -- returns <null> 
       LENGTH({fn ASCII('')}) AS AsciiEmpStrLen, -- returns <null>
       LENGTH('') AS EmpStrLen                   -- returns 0

However, certain InterSystems IRIS extensions to standard SQL treat the length of NULL and the empty string differently. The $LENGTH function returns the InterSystems IRIS internal representation of these values: NULL is represented as a defined value with length 0, the SQL empty string is represented as a string of length 0. This functionality is compatible with ObjectScript.

SELECT $LENGTH(NULL) AS NullLen,    -- returns 0
$LENGTH('') AS EmpStrLen,           -- returns 0
$LENGTH('a') AS OneCharStrLen,      -- returns 1
$LENGTH(CHAR(0)) AS CharZero        -- returns 0

Another place where the internal representation of these values is significant is in the %STRING, %SQLSTRING and %SQLUPPER functions, which append a blank space to a value. Since a NULL truly has no value, appending a blank to it creates a string of length 1. But an empty string does have a character value, so appending a blank to it creates a string of length 2. This is shown in the following example:

SELECT CHAR_LENGTH(%STRING(NULL)) AS NullLen,  -- returns 1
CHAR_LENGTH(%STRING('')) AS EmpStrLen          -- returns 2

Note that this example uses CHAR_LENGTH, not LENGTH. Because the LENGTH function removes trailing blanks, LENGTH(%STRING(NULL)) returns a length of 0; LENGTH(%STRING('')) returns a length of 2, because %STRING appends a leading blank, not a trailing blank.

ObjectScript and SQL

When an SQL NULL is output to ObjectScript, it is represented by an ObjectScript empty string (""), a string of length zero.

When an SQL zero-length string data is output to ObjectScript, it is represented by a string containing $CHAR(0), a string of length 1.

   &sql(SELECT NULL,''
        INTO :a,:b)

  WRITE !,"NULL length: ",$LENGTH(a)         // returns 0
  WRITE !,"empty string length: ",$LENGTH(b) // returns 1

In ObjectScript, the absence of a value is usually indicated by an empty string (""). When this value is passed into embedded SQL, it is treated as a NULL value, as shown in the following example:

  set x=""
  set myquery="SELECT NULL As NoVal,:x As EmpStr"
  set tStatement=##class(%SQL.Statement).%New()
  
  set qStatus = tStatement.%Prepare(myquery)
  if $$$ISERR(qStatus) {write "%Prepare failed:" do $System.Status.DisplayError(qStatus) quit}
  
  set rset = tStatement.%Execute()
  if (rset.%SQLCODE '= 0) {write "%Execute failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}

  while rset.%Next()
  {
    write "NoVal:",rset.%Get("NoVal")," length ",$LENGTH(rset.%Get("NoVal")),! // length 0
    write "EmpStr:",rset.%Get("EmpStr")," length ",$LENGTH(rset.%Get("EmpStr")),! // length 0
  }
  if (rset.%SQLCODE < 0) {write "%Next failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}

  write "End of data"

If you specify an input host variable that is not defined, embedded SQL treats its value as NULL.

In the following example, the SQL empty string with an appended blank is passed out as string of length 2:

   &sql(SELECT %SQLUPPER('')
        INTO :y )
  WRITE !,"SQL empty string length: ",$LENGTH(y)

Arithmetic Operators and Functions

Literals