I'm moving some queries out of an Access front end and creating views out of
them in SQL Server 2005 express. In some of the numeric fields, I use nz
quite often, ( i.e. nz([MyField],0)) to return a zero if the field is null.
Is there anything equivalent to this in SQL Server? Right now I'm using
CASE WHEN ... but it seems like an awful lot of script to write just to
replace null with a zero.

Any help would be greatly appreciated.

Thanks!

use coalesce or isnull

declare @v int
select coalesce(@v,0),isnull(@v,0)


Denis the SQL Menace
http://sqlservercode.blogspot.com/

On Thu, 20 Apr 2006 20:25:47 GMT, "Rico" <r c o l l e n s @ h e m m i n
g w a y . c o mREMOVE THIS PART IN CAPS> wrote:

>I'm moving some queries out of an Access front end and creating views out of
>them in SQL Server 2005 express. In some of the numeric fields, I use nz
>quite often, ( i.e. nz([MyField],0)) to return a zero if the field is null.
>Is there anything equivalent to this in SQL Server? Right now I'm using
>CASE WHEN ... but it seems like an awful lot of script to write just to
>replace null with a zero.
>
>Any help would be greatly appreciated.
>
>Thanks!
>

Hi Rico,

Use COALESCE:

COALESCE (arg1, arg2, arg3, arg4, ...)

returns the first non-NULL of the supplied arguments. You need at least
two arguments, but you can add as many as you like.

--
Hugo Kornelis, SQL Server MVP

Thanks Guys,

I wound up finding ISNULL before I had a chance to post back. (why do I
always find the solution right after I post).

Is there an argument for using Coalesce over IsNull?

Thanks!



"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message
news:j7sf42hg4b78p8u1v5nj283av4kovqivur@4ax.com...
> On Thu, 20 Apr 2006 20:25:47 GMT, "Rico" <r c o l l e n s @ h e m m i n
> g w a y . c o mREMOVE THIS PART IN CAPS> wrote:
>
>>I'm moving some queries out of an Access front end and creating views out
>>of
>>them in SQL Server 2005 express. In some of the numeric fields, I use nz
>>quite often, ( i.e. nz([MyField],0)) to return a zero if the field is
>>null.
>>Is there anything equivalent to this in SQL Server? Right now I'm using
>>CASE WHEN ... but it seems like an awful lot of script to write just to
>>replace null with a zero.
>>
>>Any help would be greatly appreciated.
>>
>>Thanks!
>>
>
> Hi Rico,
>
> Use COALESCE:
>
> COALESCE (arg1, arg2, arg3, arg4, ...)
>
> returns the first non-NULL of the supplied arguments. You need at least
> two arguments, but you can add as many as you like.
>
> --
> Hugo Kornelis, SQL Server MVP

On Thu, 20 Apr 2006 20:58:14 GMT, "Rico" <r c o l l e n s @ h e m m i n
g w a y . c o mREMOVE THIS PART IN CAPS> wrote:

>Thanks Guys,
>
>I wound up finding ISNULL before I had a chance to post back. (why do I
>always find the solution right after I post).
>
>Is there an argument for using Coalesce over IsNull?

Hi Rico,

Three!

1. COALESCE is ANSI-standard and hence more portable. ISNULL works only
on SQL Server.

2. COALESCE takes more than two arguments. If you have to find the first
non-NULL of a set of six arguments, ISNULL has to be nested. Not so with
COALESCE.

3. Data conversion weirdness. The datatype of a COALESCE is the datatype
with highest precedence of all datatypes used in the COALESCE (same as
with any SQL expression). Not so for ISNULL - the datatype of ISNULL is
the same as the first argument. This is extremely non-standard and can
cause very nasty and hard-to-track-down bugs.

--
Hugo Kornelis, SQL Server MVP

Rico (r c o l l e n s @ h e m m i n g w a y . c o mREMOVE THIS PART IN CAPS)
writes:
> I wound up finding ISNULL before I had a chance to post back. (why do I
> always find the solution right after I post).
>
> Is there an argument for using Coalesce over IsNull?

In theory, coalesce is what you always should use, because:

1) It's ANSI-compatible.
2) coalesce can accept list of several values, whereas isnull accepts
exactly two.

Unfortunately, there are contexts were isnull() is preferable, or the
only choice. The ones I'm thinking of are:
1) In definition of indexed views you may need to use isnull to make
the view indexable.
2) I've seen reports where using coalesce resulted in a poor query plan
whereas isnull did not. I should add that that was not really a plain-
vanilla query.

So despite these excpetions, I would recommend coalesce. Even if it's
more difficult to spell.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Null is not zero. Null is not a zero length string.

I believe that nulls were not designed to be placeholders for these
values.
We should be extremely careful when we convert nulls to values. Such
conversion could lead to error. Often it is persons without strong
grounding in mathematics and logic who make these conversions,
increasing the likelihood of such error. The best practice is likely to
be the exclusion of records with nulls in the columns we are processing
and to enter values in those where a value is appropriate. There may be
some cases where it's a good idea to substitute a zls for a null value,
but none comes to my mind at this time.

IMNSHO SQL would be more rigorous if it had no IsNull(Field,Value) or
corresponding Coalesce function.

[Yes, I've probably posted IsNull(Field,Value) solutions here; that was
then; this is now.]

Excellent! Thanks!


"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message
news:vttf42107btt07jbk21cvb6953kediiarp@4ax.com...
> On Thu, 20 Apr 2006 20:58:14 GMT, "Rico" <r c o l l e n s @ h e m m i n
> g w a y . c o mREMOVE THIS PART IN CAPS> wrote:
>
>>Thanks Guys,
>>
>>I wound up finding ISNULL before I had a chance to post back. (why do I
>>always find the solution right after I post).
>>
>>Is there an argument for using Coalesce over IsNull?
>
> Hi Rico,
>
> Three!
>
> 1. COALESCE is ANSI-standard and hence more portable. ISNULL works only
> on SQL Server.
>
> 2. COALESCE takes more than two arguments. If you have to find the first
> non-NULL of a set of six arguments, ISNULL has to be nested. Not so with
> COALESCE.
>
> 3. Data conversion weirdness. The datatype of a COALESCE is the datatype
> with highest precedence of all datatypes used in the COALESCE (same as
> with any SQL expression). Not so for ISNULL - the datatype of ISNULL is
> the same as the first argument. This is extremely non-standard and can
> cause very nasty and hard-to-track-down bugs.
>
> --
> Hugo Kornelis, SQL Server MVP

Read about IsNull Vs Coalesce
http://www.sqlservercentral.com/colu...weenisnull.asp

Madhivanan

On 20 Apr 2006 15:57:53 -0700, Lyle Fairfield wrote:

>Null is not zero. Null is not a zero length string.
>
>I believe that nulls were not designed to be placeholders for these
>values.
(snip)

Hi Lyle,

Thus far, I agree with yoour post.

(snip)
> There may be
>some cases where it's a good idea to substitute a zls for a null value,
>but none comes to my mind at this time.

First, you should be awarer that COALESCE and ISNULL on SQL Server, or
Nz on Access, can not just be used to replace NULL with 0 or zero length
string - you can replace them with anything you like. Common uses are
COALESCE (SomeColumn, 'n/a') in a report. Or
COALESCE (UserSpecifiedColumn, DefaultValue) in any query or view.

>
>IMNSHO SQL would be more rigorous if it had no IsNull(Field,Value) or
>corresponding Coalesce function.

I disagree with this statement. As I've shown above, COALESCE and ISNULL
can be used in very useful ways. That they might also be abused by
people who fail to think their solutions through is sad, but no reason
to abolish them. That's like forbidding cars because someone might cause
an accident while drinking and driving.

Besides, since COALESCE is just a shorthand for a specific CASE
expression, removing COALESCE from the language would have no effect;
people would just use the equivalent CASE expression.

--
Hugo Kornelis, SQL Server MVP