Does SQL Server take NULL value as an empty string?

Question

I recently read a SQL code snippet which confuses me.

declare @test nvarchar(100) = NULL
select
    case
        when @test <> '' then 1
        else 0
    end

I was quite confident that the result will be 1, since I think NULL is not equivalent to an empty string. However, the actual output is 0.

(I'm using MS SQL Server 2012 on Windows 7 64-bit)

As far as I understand, '' is an empty string which indicates the value contains 0 character, and Null means the data is in absence. But now I'm not sure about this. Can anyone help me to sort it out? Is this some exemption case?

Read this. Immagine you are sending invites to a party. '' is like getting the rsvp slip back with nothing on it. null is getting nothing back at all. — JohnHC
– JohnHC, Commented Dec 15, 2016 at 15:35
The result of that comparison is "undetermined", not true or false — Lamak
– Lamak, Commented Dec 15, 2016 at 15:36
When you have a NULL on either side of a comparison the result is neither true or false, the result is NULL. This means that in your comparison the result did not evaluate to true, it evaluated to NULL which falls into the else. — Sean Lange
– Sean Lange, Commented Dec 15, 2016 at 15:36
You could have quickly switched from <> to = and at least realized that something odd was happening here. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Dec 15, 2016 at 15:37
"since I think NULL is not equivalent to an empty string" -- For SQL Server, you're right about that part, but wrong in your conclusion. I think this can be closed as a dupe of stackoverflow.com/questions/5658457/not-equal-operator-on-null, but can you take a look yourself too to double-check if there's not some aspect in your current question that isn't answered there? — user743382
– user743382, Commented Dec 15, 2016 at 15:38

Joe Taras · Accepted Answer · 2016-12-15 15:46:23Z

3

When you use NULL for your comparison, it always will return NULL/unknown so, in fact is not true, so is false.

To analyze a NULL field you must use IS NULL

select
    case 
        when @test IS NULL then ....
        when @test <> '' then ....
        else ....
    end

or you can re-write your query as follow:

select
    case 
        when @test IS NULL or @test = '' then ...
        when @test <> '' then ....
    end

edited Dec 15, 2016 at 15:46

answered Dec 15, 2016 at 15:38

Joe Taras

15.4k7 gold badges45 silver badges59 bronze badges

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

2 Comments

Sean Lange Over a year ago

Technically speaking the comparison returns NULL, not false.

Joe Taras Over a year ago

@SeanLange: I've integrated my answer

S3S · Accepted Answer · 2016-12-15 15:37:23Z

0

Null doesn't equal ''.

Null is the absent of a value.

Null also doesn't equal Null so SELECT 1 where NULL = NULL will also return nothing.

Use this instead.

declare @test nvarchar(100) = NULL
select case when @test IS NULL then 1
            else 0
        end

answered Dec 15, 2016 at 15:37

S3S

25.2k5 gold badges29 silver badges48 bronze badges

Comments

llouk · Accepted Answer · 2016-12-15 15:37:52Z

0

Use something like this:

declare @test nvarchar(100) = NULL

select case when @test <> '' OR @test IS NULL  then 1
            else 0
        end

answered Dec 15, 2016 at 15:37

llouk

5154 silver badges15 bronze badges

Comments

JackAce · Accepted Answer · 2016-12-15 23:39:05Z

0

NULL is not the same as ''. Just like NULL is not the same as 0. NULL is a special value used to indicate that no value of the datatype is being stored.

If you want to COALESCE the NULL to a concrete value, you can use the ISNULL or the COALESCE functions in SQL Server.

DECLARE @test NVARCHAR(100) = NULL
SELECT
    CASE
        WHEN ISNULL(@test, N'') <> N'' THEN
            1
        ELSE
            0
    END

answered Dec 15, 2016 at 23:39

JackAce

1,40515 silver badges32 bronze badges

Collectives™ on Stack Overflow

Does SQL Server take NULL value as an empty string?

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related