CODEWITHSUNDEEP
sqlsql-server
devklick
devklick

Reputation: 2648

Comparing empty string with null value - SQL Server

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?

Upvotes: 5

Views: 15029

Answers (4)

alejandrob
alejandrob

Reputation: 681

It is quicker and more "Pythonic" (easier to understand by someone else who reads your code) to evaluate to the same outcome on both sides of the equal sign:

DECLARE @EmptyString VARCHAR(20) = ''

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

Both an EMPTY string and a true NULL value will yield TRUE for the search condition.

NOTE: It is not optimal, since good bussiness rules practices should clearly differenciate a NULL attribute from an EMPTY attribute. i.e.

  • spouse_field = NULL (I still don't have the information regarding entity X's marital status)
  • spouse_field = '' (empty): That specific entity is confirmed as single. Otherwise, a name would have shown up.

Upvotes: 0

debojyoti talukdar
debojyoti talukdar

Reputation: 41

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

SELECT 
    CASE WHEN @EmptyString IS NOT  @Null THEN 'Pass' ELSE 'Fail' END AS EmptyStringVsNull.

Because of ansi sql standard you can check for NUll value using IS NULL or IS NOT NULL operator.

Upvotes: 0

Nick.Mc
Nick.Mc

Reputation: 19225

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 !=

Upvotes: 8

Gordon Linoff
Gordon Linoff

Reputation: 1270843

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

Upvotes: 3

Related Questions