How to replace empty string with NULL - SQL Server Brain Basher of the Week #058

Everyone asks how to check whether the value is NULL and replace it with a different value if it is NULL because it is something that we commonly do with our applications. It can be easily done and many aware of it: All we have to do is, use IsNull function for checking and replacing it if it is NULL. However, I was asked a similar question today but it is bit different;

How can we check the value whether it is empty or not and replace it with a NULL if it is empty?

This is uncommon but important, hence it is the Brain Basher of this week.

This can be achieved by using NULLIF function. It does not check for NULLs but it returns a NULL if both supplied values are equal. See below code;

DECLARE @Variable int = 0

-- This returns NULL as both @Variable and 0 are equal
SELECT NULLIF(@Variable, 0)

-- This retuens 0 because values supplied are not equal
-- and returns the first expression
SELECT NULLIF(@Variable, 1)

For more info, read this page: https://msdn.microsoft.com/en-us/library/ms177562.aspx