null value

• esqared7

  SSC Veteran

  Points: 251

  More actions

  December 25, 2003 at 11:06 pm

  #83995

  one of my columns is retrieving null in a select statement

  How do I convert this null value to 0 so that when the select statement executes it ouputs zero

  and not null

  Thanks

• Chris Hedgate

  One Orange Chip

  Points: 25041

  More actions

  December 25, 2003 at 11:44 pm

  #487800

  SELECT COALESCE(NullColumn, 0) FROM ...

  This will output the value in NullColumn if it is not null, otherwise 0.

  --

  Chris Hedgate @ Extralives (http://www.extralives.com/)

  Contributor to Best of SQL Server Central 2002 (http://www.sqlservercentral.com/bestof/)

  Articles: http://www.sqlservercentral.com/columnists/chedgate/

  --
  Chris Hedgate http://www.hedgate.net/
  Contributor to the Best of SQL Server Central volumes
  Articles: http://www.sqlservercentral.com/columnists/chedgate/

• dplittle

  SSC Eights!

  Points: 869

  More actions

  December 29, 2003 at 8:14 am

  #487966

  Though ISNULL does do the single alternative, COALESCE has the double added benefit of

  1) being ANSI standard and supported bu more platforms and

  2) allows multiple options.

  If I'm testing several columns and want to return the first non-null one, I have to use COALESCE if the options number greater than two. As an example ...

  select COALESCE(NullCol1, NullCol2, NullCol3, DefaultValue) from table

  The first column with a non-null value is returned. If all are NULL, then the default is returned.