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 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

Recommended for you

SQL Server String Functions




 58 total views,  6 views today

Leave a Reply

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