CODEWITHSUNDEEP
sqlsql-servernull
ChangeMyName
ChangeMyName

Reputation: 7408

Does SQL Server take NULL value as an empty string?

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?

Upvotes: 0

Views: 544

Answers (4)

JackAce
JackAce

Reputation: 1405

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

Upvotes: 0

Joe Taras
Joe Taras

Reputation: 15379

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

Upvotes: 3

llouk
llouk

Reputation: 513

Use something like this:

declare @test nvarchar(100) = NULL

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

Upvotes: 0

S3S
S3S

Reputation: 25112

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

Upvotes: 0

Related Questions