Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 660
DNac Avatar asked Mar 08 '23 09:03

DNac


1 Answers

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

like image 74
Rawitas Krungkaew Avatar answered Mar 20 '23 14:03

Rawitas Krungkaew