September 6, 2006

Nulls, Blanks and Empty Strings in SQR

String and date comparisons in SQR can be a little tricky at times. For example, trailing spaces are significant; 'Y' != 'Y '. To avoid subtle bugs, SQR programmers get into the habit of using RTRIM on string variables. And then there are nulls, blank values, and empty strings. How do they relate to each other?

SQR provides two built-in functions that can help with this. ISNULL() returns 1 (which can be interpreted as "true") if its argument is a null value. In PeopleSoft systems this would normally apply to dates only, since character fields are defined as NOT NULL and therefore cannot contain null values. At least in the Oracle version, an empty string ('', no spaces) is handled by SQR the same way as a null value.

The other function is ISBLANK(). It returns 1 if a value is null, an empty string, or a string containing blank spaces and/or whitespace characters (tabs, carriage returns and linefeeds).

   let  = ' ' || chr(9) || chr(13) || chr(10) || ' '
   if isblank()
     show 'Whitespace is blank'   ! This is printed
   end-if

Note that two values can both pass the ISBLANK() test but still be unequal, and that a value could pass ISBLANK() but not ISNULL().

Until next time...