Home » SQL COALESCE()

SQL COALESCE()

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, firt value is null yet coalesce 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

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

Use of coalesce 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 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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.