Geek Answers Handbook

Why is the output of select nullif (0, ``) equal to NULL (expected 0)?

Why is the output of select nullif(0,'') equal to NULL? (Expected 0).

How come 0 equals ''?

When I use select nullif (convert (varchar, 0), ''), it actually returns the expected 0.

NULLIF The official page says that the function compares two scalar expressions. 0 is not scalar? Please tell me what I am missing in my understanding?

+8
sql-server tsql
source share
2 answers

Integers are higher in data type priority , so varchar is converted to int. An empty string converted to int, 0 , and from there it is pretty obvious ( 0 == 0 ).

0 == 0 , so NULLIF(0, 0) => NULL (since NULLIF(a, b) returns NULL iff a == b )

When you do nullif(convert(varchar,0),'') , you just do NULLIF('0', '') . Obviously, a string containing only 0 and an empty string is not equal, so you get 0 .

A more detailed explanation is that two different types cannot be compared. You cannot compare a string with an integer, or with a string and with a floating point, or with an integer and with a floating point, or so on. This means that in order to compare different types, there must be some implicit casting rule. In this case, it happens that if you compare the string (well, technically varchar) and int, varchar is converted to int implicitly. This is much easier to see if you consider the following:

 SELECT CONVERT(INT, ''); IF '' = 0 SELECT 'Equal' ELSE SELECT 'Not equal';

As you will see, the conversion gives an integer value of 0. In addition, this leads to a comparison between two estimates with true.

+11
source share

Is it possible that the nullif function sets both values ​​for either bit or int , which is implied by the first value?

If so, they will both evaluate to 0, so nullif will return null , as you can see.

This explains why converting to varchar solves the problem.

+1
source share

More articles:


All Articles