Menu
is there a function in SQL Server 2005 that returns NULL [or a boolean value] if any of the arguments (of any type) is NULL, which would save me from writing IF a IS NULL OR b IS NULL OR c IS NULL ....
861
The obvious differences are that coalesce will return the first non-null item in its parameter list whereas nvl only takes two parameters and returns the first if it is not null, otherwise, it returns the second. It seems that NVL may just be a 'Base Case" version of coalesce.
COALESCE is useful when you have unknown number of values that you want to check. IFNULL is useful when you select columns and know that it can be null but you want to represent it with a different value. Therefore, the two functions are vastly different.
Solution – To replace the null values with another values we can use the COALESCE() function in SQL. The COALESCE() function returns the first Non-Null value in a list. This query returns 5 as this is the first Non-Null value in this list.
COALESCE and ISNULL advantage 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.
Here is a moderately unpleasant way of doing it:
set ansi_nulls off
if (null in (a, b, c, d, e) print 'got a null'
set ansi_nulls on
Since NULLs propogate you could do:
(cola + colb + colc) is null
assuming all compatible data types
40
No, the closest you an get is NULLIF(), but that's not what you want. I'd just stick to using the OR statement here.
30
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
