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](http://www.w3schools.com/sql/sql_null_values.asp). 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, Dec 15 '16 at 15:35
The result of that comparison is "undetermined", not true or false — Lamak, Dec 15 '16 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, Dec 15 '16 at 15:36
You could have quickly switched from `<>` to `=` and at least realized that something odd was happening here. — Damien_The_Unbeliever, Dec 15 '16 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 https://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? — , Dec 15 '16 at 15:38
Tony Hoare, the inventor of the null, once described it as a [billion dollar mistake](https://en.wikipedia.org/wiki/Tony_Hoare). I think this kind of confusion backs up his point! — David Rushton, Dec 15 '16 at 15:59

Joe Taras · Answer 1 · 2016-12-15T15:46:23.260

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 '16 at 15:46

answered Dec 15 '16 at 15:38

Joe Taras

15,166
7
42
55

3

Technically speaking the comparison returns NULL, not false. – Sean Lange Dec 15 '16 at 15:44
@SeanLange: I've integrated my answer – Joe Taras Dec 15 '16 at 15:46

score 0 · Answer 2 · answered Dec 15 '16 at 15:37

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

score 0 · Answer 3 · answered Dec 15 '16 at 15:37

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 '16 at 15:37

llouk

513
4
15

score 0 · Answer 4 · answered Dec 15 '16 at 23:39

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

Does SQL Server take NULL value as an empty string?

4 Answers4