T-SQL NULLIF returns NULL for zero

Question

Why the script below returns NULL instead of 0?

DECLARE @number BIGINT = 0;

SELECT  NULLIF(@number, '');

According to the MSDN, it should return 0:

NULLIF
Returns a null value if the two specified expressions are equal.

For SQL server, 0 and '' is considered the same (=equal)? What is the logic behind?

meta-dupe: [Why 0 is equal to empty string?](https://dba.stackexchange.com/questions/101884/why-0-is-equal-to-empty-string) — underscore_d, Dec 12 '17 at 09:55
Possible duplicate of [SQL Server 2012 blank string comparison with 0](https://stackoverflow.com/questions/25142303/sql-server-2012-blank-string-comparison-with-0) — underscore_d, Dec 12 '17 at 09:55

score 5 · Accepted Answer · answered Dec 12 '17 at 09:47

When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence.

SELECT CONVERT(bigint, '')
SELECT CONVERT(float, '')
SELECT CONVERT(date, '')

0
0
1900-01-01

https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql

score 1 · Answer 2 · answered Dec 12 '17 at 09:48

As BOL states: "the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence." You've got two different datatypes, bigint and nvarchar. In order to compare the two, they have to be the same datatype. Following the rule described, the nvarchar is implicitly converted to bigint. Try select convert(bigint, ''), you'll find it results in 0. So they are the same.

score 0 · Answer 3 · answered Dec 12 '17 at 09:48

This is the result of implicit conversion. In some cases a string value can be converted to an integer (such as empty string is converted to 0).

Essentially SQL Server tries to match the data type of the two expressions first, then it checks the values.

DECLARE @number BIGINT = 0;

SELECT 
    CONVERT(BIGINT, '')
    , NULLIF(@number, '')
    , NULLIF(@number, CONVERT(BIGINT, ''))

score 0 · Answer 4 · answered Dec 12 '17 at 09:49

0

It has converted '' to the integer which is 0, as integer has higher precedence in data type. Check the example below how '' become 0

SELECT CONVERT(INT, '')  -- 0
SELECT CAST('' AS INT)   -- 0

answered Dec 12 '17 at 09:49

Shushil Bohara

5,556
2
15
32

score 0 · Answer 5 · answered Dec 12 '17 at 09:53

This script should return null and it is true! The reason behind it is '' is a string, so it will get implicitly casted to an integer value when comparing it with an integer as you are doing now! In general, you're asking for trouble when you're comparing values of different data types, since implicit conversions happen behind the scene.

T-SQL NULLIF returns NULL for zero

5 Answers5