SQLTeam.com | Weblogs | Forums

Int Column - Blank behavior


I'm curious as to why SQL server interprets a "zero" (0) as a blank in SQL Server. Understandably, if you have an int column, it should not be blank. I'm not referring to nulls...I understand how a null would be handled. But if you have a physical value of zero in an int column, it's strange to me that when testing with a case expression or through the where clause, that SQL Server would interpret a zero as blank. Here's an example that I set up:

declare @status table (intvalue int)
insert into @status (intvalue)

SELECT intvalue,
case when intvalue='' then 'I''m Blank' end,
case when intvalue=0 then 'I''m Zero' end,
case when intvalue is null then 'I''m Null' end
FROM @status

I'd expect that when testing the "0" it would only return a result as "0". Instead, it is interpreting 0's as blank or zero and likewise, interpreting blanks as blank or zero. (3 row(s) affected) intvalue ----------- --------- -------- -------- 0 I'm Blank I'm Zero NULL 0 I'm Blank I'm Zero NULL NULL NULL NULL I'm Null (3 row(s) affected) Is this an option setting or is there some other explanation? Thanks!


Implicit conversion:


You can't really store an empty string in an int column, so SQL converts it in your insert statement.


G...thanks! I guess my confusion lies with the fact that it interprets a 0 as a blank. If you look at the case expressions, in the "case when intvalue='' then 'I''m blank' end" expression, it interprets the "0" as a blank on row 1 of column 2 in the SELECT.


that's not exactly what happens. Actually another implicit conversion takes place, this time from int to char, in the CASE expression. So the int is converted to a char, then the char is compared.


Aha! That makes sense...was sort of thinking along those lines, but wanted to be sure. Thanks a lot for your help!