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?

I've read your question twice and still don't understand what you are asking. Seems like you are confused as to what the ISNULL and NULLIF built in functions do. — Zohar Peled
– Zohar Peled, Commented Apr 22, 2018 at 13:07
I think you have confused yourself and everybody else with this question. Can you create a logic table of all the combinations (A side and B side) with expected results and update your question with it included? — MJH
– MJH, Commented Apr 22, 2018 at 13:12

Nick.Mc · Accepted Answer · 2018-04-22 13:06:49Z

8

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

answered Apr 22, 2018 at 13:06

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2018-04-22 13:06:33Z

3

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

answered Apr 22, 2018 at 13:06

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Comments

MJH · Accepted Answer · 2018-04-22 18:28:52Z

0

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.

edited Apr 22, 2018 at 18:28

MJH

1,7481 gold badge12 silver badges20 bronze badges

answered Apr 22, 2018 at 17:31

debojyoti talukdar

412 bronze badges

1 Comment

MJH Over a year ago

@Null is a parameter (I know) you can tell by the @ symbol in front of it.

alejandrob · Accepted Answer · 2021-12-09 16:02:59Z

0

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.

edited Dec 9, 2021 at 16:02

answered Dec 9, 2021 at 15:48

alejandrob

6818 silver badges6 bronze badges

Collectives™ on Stack Overflow

Comparing empty string with null value - SQL Server

ISNULL()

NULLIF()

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

ISNULL()

NULLIF()

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related