COALESCE function in SQL Server
This article introduces COALESCE function. It is available in SQL server 2008 and above. It can be used to simplify a CASE
expression. This function accepts a number of arguments. It evaluates the arguments in order they appear until a argument evaluates
to non-null value. This non-null value is then returned by function. If all of the arguments evaluates to null, then null
value is returned. Arguments can be expression of any type.
This article introduces COALESCE function. It is available in SQL server 2008 and above. It can be used to simplify a CASE expression. This function accepts a number of arguments. It evaluates the arguments in order they appear until a argument evaluates to non-null value. This non-null value is then returned by function. If all of the arguments evaluates to null, then null value is returned. Arguments can be expression of any type.
Example:
Create table TABLE1
(
col1 varchar(10),
col2 int,
col3 int,
col4 int
)
Insert into TANLE1
values
('item1',9500,null,null)
('item2',null,6500,null)
('item3',null,null,1000)
('item4',null,null,null)
Select Col1,
COALESCE(Col2,Col3,Col4,0)
From
TABLE1
Note that the three columns and zero are used in the function; if all of the columns are NULL, function will return zero.
COALESCE is a standard function defined by the ISO\ANSI SQL. The data type of a COALESCE expression is the data type of the input arguments with the highest data type precedence. If all inputs are the untyped NULL literal then it gives error.
Select COALESCE('b',1);
Above expression generates conversion error. As integer has a higher data type precedence than a character string; COALESCE function tries to convert first argument to data type int.