CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2017nullif
DNac
DNac

Reputation: 2793

T-SQL NULLIF returns NULL for zero

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?

Upvotes: 4

Views: 3705

Answers (5)

Shahrzad Jahanbaz
Shahrzad Jahanbaz

Reputation: 108

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.

Upvotes: 0

Shushil Bohara
Shushil Bohara

Reputation: 5656

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

Upvotes: 0

HoneyBadger
HoneyBadger

Reputation: 15150

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.

Upvotes: 1

Pred
Pred

Reputation: 9042

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, ''))

Upvotes: 0

Ray Krungkaew
Ray Krungkaew

Reputation: 6977

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

Upvotes: 5

Related Questions