NULLIF Expression

Returns a null value if the two specified expressions are equal; otherwise, returns the first expression.

Restriction: The BASE file format processes a null value as DOUBLE values in some situations and as a blank string in other situations. For more information, see How FedSQL Processes Nulls and SAS Missing Values.

Syntax

Arguments

expression

specifies any valid SQL expression.

Data type All data types are valid.
See <sql-expression>
FedSQL Expressions

Details

The NULLIF expression compares two SQL expressions and, if they are equal, returns a null value. The NULLIF expression enables you to replace a missing or inapplicable value with a null value and to use SQL's behavior for null values.

Comparisons

The NULLIF expression is a shorthand syntax for a special CASE expression. For example, if a student misses a test, a -1 is entered in the GRADES table. To replace this -1 with a null value, you could use the following CASE code.
update grades
   set testscore =
    CASE
      when testscore = '-1' then null
      ELSE testscore
     END;
The following code uses the shorter NULLIF expression.
update grades
   set testscore = NULLIF(testscore, '-1');
The IFNULL function compares two SQL expressions and returns the second SQL expression if the first SQL expression is a null value. The NULLIF expression compares two SQL expressions and returns a null value if the two SQL expressions are equal.

Example

missingLong= '.L';
update worldcitycoords
set longitude = nullif(missingLong, '.');
select city
   from worldcitycoords
      where Longitude='.L';

See Also

Functions:
Last updated: February 23, 2017