If its arguments are equal,NULLIF returns NULL , else it returns its first argument, scalar_expression_1.
For the following full CASE expression NULLIF is a shorthand expression :
CASE WHEN scalar_expression_1=scalar_expression_2 THEN NULL ELSE scalar_expression_1 END
The following examples show queries on the following table:
CREATE TABLE Membership
(FullName CHARACTER(39)
,Age SMALLINT
,Code CHARACTER(4) );
Example 1:
The following is the Teradata SQL NULLIFZERO(Age) function ANSI- compliant form of theand is more versatile.
SELECT FullName, NULLIF (Age,0) FROM Membership;
Example 2:
In the following query, blanks indicate no value.
SELECT FullName, NULLIF (Code, ' ') FROM Membership;
Example 3:
The following example uses NULLIF in an expression with an arithmetic operator.
SELECT NULLIF(Age,0) * 100;