Menu
I have a simple SQL query (SQL Server 2005) where I'm selecting from a table that contains multiple columns that have BIT values. These columns are nullable so can contain NULL, 0 or 1.
There are a fair number of these columns and in my query I want to return zero if the value is NULL. I'm currently using ISNULL like so:
SELECT Name, Age, ISNULL(LikesOranges,0), ISNULL(LikesApples,0), ISNULL(LikesPears,0)
FROM FoodPreferences
As I've mentioned, there are a lot of these BIT columns (much more than in the simple example above). Is there a way I can use ISNULL over multiple columns like this:
SELECT ISNULL(*,0) FROM FoodPreferences
The above query doesn't work but you get what I'm trying to do - so I can avoid having to write an ISNULL statement for each column,
Thanks.
573
The IsNull function can check only if one value is null. It cannot check null for multiple values. That means it is not capable of handling the functionality of checking if the first parameter is null and then moving on to check the next parameter for null.
COALESCE and ISNULLadvantage that COALESCE has over ISNULL is that it supports more than two inputs, whereas ISNULL supports only two. Another advantage of COALESCE is that it's a standard function (namely, defined by the ISO/ANSI SQL standards), whereas ISNULL is T-SQL–specific.
So yes, you can nest ISNULL functions two, three, four, or more levels deep as you need. A SELECT statement that returns a single row containing a single column can (usually) be used as an expression. And multiple SELECT statements (subqueries) within a query can reference the same table(s) and same column(s).
Try this:
SELECT COALESCE(LikesOranges, LikesApples, LikesPears) AS MyBit FROM FoodPreferences
This will return the first non-null value. If all fields are NULL the result is NULL.
UPDATE:
And the conclusion is:
SELECT ISNULL(COALESCE(LikesOranges, LikesApples, LikesPears),0) AS MyBit FROM FoodPreferences
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
