WHERE IsNull(myField,'') = ''
However, I just noticed that this is also return records where myField = 0
Does anyone know why?
What is the best and most common way to check if a field is null?
The best way to check if a field is null is
WHERE myField IS NULL
Are you by chance checking for a "blank" field instead of a null field?
|||Zero is not null.
Try this:
Code Snippet
select *
from MyTable
where MyField IS NULL
|||Arnie,The fact that rows with MyField = 0 are returned is not a problem with the query. If the type of MyField is integer and its value is zero, the query will test 0 = '', which is true. This is because '' is implicitly converted to integer in the comparison, and in SQL Server, cast('' as integer) has the value zero.
Steve Kass
Drew University
http://www.stevekass.com
|||
Steve,
...such rows would indicate that there is a problem in the query.
Thanks for the clarification. I was referring to the nature of the query -not the evaluation of implicit zero. The OP wanted NULL rows, so the way the query was written (invoking implicit conversions) would not satisfy his/her needs.
I could have more precise, but I dropped it. I'm glad you jumped in to dispel any misconceptions I may have created.
