Every once in a while I’ll run across an article on the NULLIF function. If you haven’t seen this function before, it takes two arguments:
SELECT NULLIF('A', '');
If the two values are different, then the first value is returned (In this example, A is the returned value). If the values are the same, then NULL is returned. T-SQL and Postgres both have this function.
I’ve known about NULLIF, but I’ve never actually used it before. You could always write a CASE statement to get the same functionality, so I started to wonder about use cases for this function.
The most common use seems to be with removing empty length strings and replacing them with NULL. For example if you had a Customer table with a Middle Name column, you can make sure any empty length string values are converted to NULL:
SELECT NULLIF(MiddleName, '') FROM Customer;
I also ran across this interesting article from Ben Nadel on using NULLIF to prevent divide by zero errors. Of course, trying to divide a number by zero will give you an error, but dividing a number by NULL will return NULL. So adding NULLIF(value, 0) to the divisor will prevent the error if your value is zero.
I would be interested to see any other uses for NULLIF.