T-SQL Tutorials - TSQLTutorials.com

COALESCE - Replacing NULL values


Introduction

Often when writing queries you may want to check for NULL values and replace them with a non-NULL value. The COALESCE function is a good canidate to use for this purpose. It allows you to check if a value is NULL and then replace that value with a static value, or with another column(s). The following are two example syntaxes of using the COALESCE Function:

  • SELECT COALESCE([column1],'String') FROM table

  • SELECT COALESCE([column1],[column2],'String') FROM table



Examples

An example of using the COALESCE function would be to check a column for a NULL value and replace that NULL value with a value. In this example we can check a field called 'LastUpdateDate', which contains the last time a table record has been updated. If the record has never been updated the column will contain a NULL value. Using COALESCE we can replace the value with a default date
SELECT COALESCE([LastUpdateDate],'1/1/1900') AS [LastUpdateDate]
FROM UserTable

Another example of using the COALESCE function would be to check two related columns and use the first available value (NON-NULL value) from those columns to populate a new column. This time we will use the same 'LastUpdatedDate' column, but we will also use another column in the table called 'CreateDate', which contains the value that the record in the table was created. For this example we will assume that the CreateDate will always be populated. So for our query we will use the LastUpdateDate, unless it's value is NULL, otherwise we will use the CreateDate.
SELECT COALESCE([LastUpdateDate],[CreateDate]) AS [LastUpdateDate]
FROM UserTable

A final example of using the COALESCE function would be to check multiple related columns and use the first available value (NON-NULL value) from those columns to populate a new column. This time we will use the same 'LastUpdatedDate', and 'CreateDate' column, but this time we will assume that the 'CreateDate' column can also contain a NULL value. So for our query we will use the LastUpdateDate, unless it's value is NULL, otherwise we will use the CreateDate. If the CreateDate is NULL, then we will use a default date
SELECT COALESCE([LastUpdateDate],[CreateDate],'1/1/1900') AS [LastUpdateDate]
FROM UserTable


SQL Server 2014 Standard w/ 10CAL Edition, Media & License | 32 bit & 64 bit

Price: $299.99
Buy It Now
Brand New - Microsoft SQL Server 2012 Standard Edition SP3 and 10 CAL License

Price: $400
Buy It Now
SQL Server 2014 Standard Edition, 16 Core Processor License | unlimited USER CAL

Price: $599.99
Buy It Now
MSFT SQL Server 2008 R2 Standard Edition 32/x64 | 25CAL, Media, License

Price: $299.99
Buy It Now
MSFT SQL Server 2012 Standard Edition, 32bit & 64bit, Media & License w/10CAL

Price: $399.99
Buy It Now
Lenovo Microsoft SQL Server 2016 CAL (1 User)

Price: $199.99
Buy It Now







Copyright 2017 - TSQLTutorials.com