Teradata NULL functions

The following are the NULL functions in Teradata sql:

1) NULLIFZERO

This will change a zero value to a NULL value.

2) NULLIFSPACES

This will change a space value to a NULL value.

3) ZEROIFNULL

This will change a NULL value to a zero.

4) NULLIF = 0

This will change a zero value to a NULL.

ex: define c1(decimal (5,2), NULLIF = 0);

5) NULLIF = ' '

This will change a space value to a NULL.

ex: define c1(char (2), NULLIF = ' ');

6) NULLIF = '?'

This will change a '?' value to a NULL.

ex: define c1(char (2), NULLIF = '?');

7) NULLIF = '40616263'XC

The '40616263'XC is a hexadecimal representation of '@abc'. Therefore, this will change a '@abc' value to a NULL.

ex: define c1(char (4), NULLIF = '40616263'XC );

8) CASE WHEN :c1 = '?' THEN NULL ELSE :c1 END

This will change a '?' to a NULL.

9) NULLIF(:c1,'?')  

This will change a '?' to a NULL. This is an abbreviation of the case command.

10) CASE WHEN c1 IS NOT NULL THEN c1 ELSE 0 END

This changes a null value to a zero.

Tweet