Comparing empty string with null value - SQL Server

Question

I need to compare two columns, and am hitting issues when comparing NULL against an empty string. Put simply, this is what I'm looking at.

DECLARE @EmptyString VARCHAR(20) = '', -- Could have valid VARCHAR data, empty string, or NULL
        @Null VARCHAR(20) = Null; -- Could have valid VARCHAR data, empty string, or NULL

SELECT 
    CASE WHEN @EmptyString != @Null THEN 'Pass' ELSE 'Fail' END AS EmptyStringVsNull

In this case, because we all know that an empty string and a null column value are different, I would hope to see the result come back as 'Pass', however it doesn't.

From Martin's asnwer here, he explains that that a comparison like this results to UNKNOWN, rather than TRUE or FALSE, which clarifies the reason I am seeing these results, but I need find a solution to this. I know there must be a simply way around this that I'm missing...

I know that there are a few built in functions such as ISNULL() and NULLIF(), however I don't know if these can help in this situation...

ISNULL()

If we use the ISNULL() function to set null values to an empty string, then the comparison wont work as an empty string is equal to an empty string, for example

DECLARE @EmptyString VARCHAR(20) = '',
        @Null VARCHAR(20) = Null;

SELECT 
    CASE WHEN ISNULL(@EmptyString, '') != ISNULL(@Null, '') THEN 'Pass' ELSE 'Fail' END AS EmptyStringVsNull

This also returns 'FAIL', so this is a no go. I could always use ISNULL to convert this to a different string, but this still isn't suitable, as the empty string may have a different value which by chance could match whatever we decide to convert null values to.

NULLIF()

DECLARE @EmptyString VARCHAR(20) = '',
        @Null VARCHAR(20) = Null;

SELECT 
    CASE WHEN NULLIF(@EmptyString, '') != NULLIF(@Null, '') THEN 'Pass' ELSE 'Fail' END AS EmptyStringVsNull

If we use the NULLIF() function to convert empty strings to null, our comparison still doesn't return true. This is because, as explained in the linked post, comparing null values results in UNKNOWN.

With the simple SQL example above, how can I check that a NULL value is not equal to an empty string?

Answer 1

Your first example returns fail because you have the wrong operator. If you want to see if something equals something else you use =, not !=

Here is the code that proves that NULL can be compared to '':

DECLARE @EmptyString VARCHAR(20) = '',
        @Null VARCHAR(20) = Null;

SELECT 
    CASE WHEN ISNULL(@EmptyString, '') = ISNULL(@Null, '') 
    THEN 'Pass' ELSE 'Fail' 
    END AS EmptyStringVsNull

It returns pass because you use =, not !=

Answer 2

Just reverse the comparison:

SELECT (CASE WHEN @EmptyString = @Null THEN 'Fail' ELSE 'Pass' END) as EmptyStringVsNull

The only complication is if you want two NULL values to be the same. If so:

SELECT (CASE WHEN @EmptyString = @Null OR (@EmptyString IS NULL AND @Null IS NULL)
             THEN 'Fail' ELSE 'Pass'
        END) as EmptyStringVsNull