SQL COALESCE function returns the first non-null value in a list.Â
- A list must have at-least one non null value otherwise it returns an error.
- Values must be of same data-type.
- It Always evaluates for an integer first, an integer followed by character expression yields integer as an output.
SYNTAX
COALESCE (val1, val2, ...)
Lets look at an example of COALESCE in SQL Server .
Following statement uses COALESCE function and returns the first non null in a list, that is 5.
select COALESCE(null, 5, 'SQL', 'SQL server') as first_non_nullval
As you can see, first value is null yet COALESCE function returns 5 that is a second non null value in a list.
Following statement uses coalesce function that returns the first non null value in a list , that is ‘SQL’
select COALESCE(null, 'SQL',null, '2018', 'SQL server') as first_non_nullval
Some possible error with COALESCE Function
Coalesce functions always evaluates for an integer first, an integer followed by character expression yields integer as an output.
In following statement, Coalesce function gives an error of converting varchar data type to int data type .
select COALESCE(null, 'SQL, 1 )Â Â
If a list having all null values
If you pass all null values to coalesce function, it returns an error as given belowÂ
select COALESCE(null, null ) as first_non_nullval
Using a COALESCE function in a string concatenation
As you know, concatenate a string value with null values always returns a null value .
In following SQL Statement returns null value , when a null value is concatenated with non null string values.
select 'SQL Server ' + '' + null + '2018' as Output
Using COALESCEÂ Function, you can handle null values by replacing it with any other values.
Following SQL statement should still concatenate all the string but ignore null values by replacing it with space ”
select 'SQL Server ' + '' + COALESCE(null ,'')+ '2018' as Output
Also Read..
