Why is the number 0 evaluating to blank space
This is something that puzzled me before, but I never found an explanation for it. I have a SQL Server 2008 database column that is of type smallint
. I want to search for any lines where the value is NULL
or is empty, so I say this:
SELECT *
FROM products
WHERE warranty_dom IS NULL
OR warranty_dom = ''
This returns rows with value 0
So why 0
is it considered equivalent ''
?
+3
source to share
1 answer
0 is not considered "by itself". Instead, '' is implicitly cast to an integer, and this cast makes it equal to 0.
Try it yourself:
SELECT CAST(0 AS varchar) -- Output: '0'
SELECT CAST('' AS smallint) -- Output: 0
Also, as mentioned elsewhere: If warranty_dom is of type smallint, then it is not possible for it to be empty in the first place.
+12
source to share